Excel 2010 Can I use conditional formatting on cells with a range of numbers in each cell?

Question

With Excel 2010 can I use conditional formatting on cells with a range of numbers in each cell? For example in cell B2 the information is 8-11. In cell B3 the information is 21-24 and so on (these are not dates. They represent between 8 and 11 for example). I want to format it so that cells with number ranges anywhere between 0-20 are one color, 21-26 are another color and so on. I have completed many tutorials on how to do this with single numbers (whole numbers, decimals, fraction, percentages etc) but I can't find anything anywhere on how to do this with a range of numbers in each cell. Is it possible? If so could you please explain the process to me or point me in the direction of a tutorial? Thank you.

garbo7441 · Accepted Answer

Data entered in a cell as '8-11' is not a numeric entry to Excel, it is a text entry.  Thus, you cannot parse this kind of data in numeric terms.  This would require the use of VBA to evaluate the cell contents and fill the color accordingly.

Format column B as 'Text' down through the last row you wish to monitor.

Copy the following event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
If Target.Column = 2 Then
Target.Interior.ColorIndex = xlNone
tVal1 = Right(Target, Len(Target) - Application.Find("-", Target))
If tVal1 < 20 Then
Target.Interior.ColorIndex = 35
ElseIf tVal1 < 27 Then
Target.Interior.ColorIndex = 36
End If
End If
errhandler:
End Sub

Select the worksheet containing the data  to evaluate and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button w/white 'x' - top right).

Enter '8-11' in any cell in column B and it will fill light green.  Enter 21-26 in any other cell in column B and it will fill light yellow.

If you wish to use different colors, change the '35' and '37' to different numbers.  Here is a 'color guide':

http://dmcritchie.mvps.org/excel/colors.htm

If you have additional ranges to add, please advise.

? · Answer

Yes, but you may need to use text functions as part of the condition.

Excel also normally assigns values like "8-11" and "21-24" the date format, even if you don't see them that way.

You are probably better off using two cells for the range (start value, end value), in which case you can use normal conditional statements.
B2: 8
C2: 11
Condition 1: Value is less than 21
Condition 2: Value is equal to or greater than 21

What should happen if a range straddles a boundary?

csarxex · Answer

yes,  u can doit usin formules into de conditioning text, like 
=rigt(b2,2) <12 ; and  etc,

Trending News

Excel 2010 Can I use conditional formatting on cells with a range of numbers in each cell?

3 Answers