Formatting question - Excel 2007?

Question

Formatting question - Excel 2007

Hi,
I have three long lists of data. I want to highlight numbers in one list when they fall between two numbers in the other lists, e.g. in the set below I would want to highlight A1 and A5. I can't work out a way to do this through conditional formatting without going through and putting a 'between' rule on each row, and as I have 3000 rows I don't want to do that...

A B C
1 14 10 15
2 50 22 25
3 5 7 11
4 5 3 28

if there's another way I can pull out the values assides highlighting conditional formatting that's great too. Whatever works.
Thanks so much for your input

Answer 1

Actually, the value in A1, A3, and A4 would all highlight based on being between ANY value in B and C. The '5' in row 3 is also between the values in B4 and C4.



Given that, the following event handler will do as you wish.



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





Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & LastRow).Interior.ColorIndex = xlNone

For i = 1 To LastRow

For j = 1 To LastRow

If Cells(i, "A").Value > Cells(j, "B").Value And _

Cells(i, "A").Value < Cells(j, "C") Then

Cells(i, "A").Interior.ColorIndex = 37

Else

End If

Next j

Next i

End Sub





Select the worksheet containing the data and right click the sheet tab.



Select 'View Code'.



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



Close the VBE by clicking the red close button, top right.



Select any cell and the data in column A will fill based on the criteria.



Note: If you wish to have a different fill color than light blue, change the 37 value in line 10 to a different number before copying the event handler.



Here is a link to the different codes for Excel's colors:



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

Answer 2

do you mean that you want to use conditional formatting on the whole list, and you dont want to go through every thing on the list?



If so, you can do conditional formatting on a whole colmun. select the column heading, EG column A and do the conditioanl formatting on that, this will highlight anything in that column that meets the criteria, so you dont have to put a rule on each cell.

Answer 3

Use a formula for your conditional formatting. First, highlight column A so the formatting applies to the whole column, then use this formula in your CF:



=AND(A1>$B1, A1<$C1)



If you want it highlighted if the number in A1 = either B1 or C1, then change the >, < to >= , <=