More Than 3 Conditional Formating

[Michele]

I have a spreadsheet that I put to use and works brilliantly thanks to help I received here, Thank You, But now I find myself needing to add a condition but seem to be limited to only 3 conditions.

I have tried to use some code I found here but they are for specific values only and my need is much more simple in that I just want the row to change colors based on ANY value of 4 different cells. Doesn't matter WHAT is in these 4 cells, just as long as they are NOT EMPTY.

Here is my idea, I am using collumns A-U as my ROW. I want the ROW to be colored as listed below:

IF B1 = NOT EMPTY make row RED
IF H1 = NOT EMPTY make row YELLOW
IF N1 = NOT EMPTY make row GREEN
IF U1 = NOT EMPTY make row NO COLOR (white)

Letters, numbers and even zeros are ok, if ANYTHING is entered into the cell above, the row takes on the new color, overriding any previous colors.

So if B1 has a X in it and H1 has a X in it then the row will be YELLOW.
Likewise, wether there is nothing in cells B, H and N but IS a X in U1, the row should be NO COLOR.

Please let me know if I can provide more info, thank you, I will be standing by to answer questions,

M

 

[JamesW]

Hi Michele,

You can actually have 4, with the 4th being the 'default' value - mark the cell with a background colour of your choice (one you are not using in the conditional format) and when it does not match any of those conditions it will change to the default colour.

Does this help you? If not we can always use VBA.

 

[Michele]

Quess I am trying to figure out how to have a 4th ??? I have the Conditional Formating window up now and there are 3 conditions set up and the ADD button is grayed out, no way to select ADD a new condition. BTW, I am using Excel 2000, thank you, M

 

[JamesW]

So, say you have the following conditions in the Conditional Format window:

1. IF B1 = NOT EMPTY make row RED
2. IF H1 = NOT EMPTY make row YELLOW
3. IF N1 = NOT EMPTY make row GREEN

Close the window and in the row where you applied the conditons set the background colour to NO COLOUR - this will tell Excel that if non of the above are met then resort to the background colour of the row.

 

[JamesW]

If all else fails, try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1,H1,N1,U1")) Is Nothing Then
        If Range("B1").Value <> "" Then Rows([B]2[/B]).EntireRow.Interior.ColorIndex = 3
        If Range("H1").Value <> "" Then Rows([B]2[/B]).EntireRow.Interior.ColorIndex = 6
        If Range("N1").Value <> "" Then Rows([B]2[/B]).EntireRow.Interior.ColorIndex = 4
        If Range("U1").Value <> "" Then Rows([B]2[/B]).EntireRow.Interior.ColorIndex = -4142
    End If
End Sub

To add this code, press ALT+F11 when in your spreadsheet, double click on the sheet name on the left in the VBA window and copy and paste the above.

Change Rows(2) to which ever row you want to highlight.

It's not brilliant, but should do the job.

 

[Michele]

I just edited your code and it won't work for me, I tried it as provided and it won't work either.... what did I do wrong, I am applying this to the entire aheet but wanted to test it on row 100 before I did.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1,H1,N1,U1")) Is Nothing Then
If Range("B100").Value <> "" Then Rows(100).EntireRow.Interior.ColorIndex = 3
If Range("H100").Value <> "" Then Rows(100).EntireRow.Interior.ColorIndex = 6
If Range("N100").Value <> "" Then Rows(100).EntireRow.Interior.ColorIndex = 4
If Range("U100").Value <> "" Then Rows(100).EntireRow.Interior.ColorIndex = -4142
End If
End Sub

 

[JamesW]

Hi,

Change

If Not Intersect(Target, Range("B1,H1,N1,U1")) Is Nothing Then

to

If Not Intersect(Target, Range("B100,H100,N100,U100")) Is Nothing Then

What this is doing is saying "if you make a change in cells B100, H100, N100 or U100 then do stuff, otherwise do nothing"

 

[Michele]

OK !!! That works fine !!! Now, How to just make the row color changes stop at COLUMN U

and,

How to apply to entire sheet ?

Thanks, M

 

[JamesW]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B100,H100,N100,U100")) Is Nothing Then
        If Range("B100").Value <> "" Then Range("A100:U100").Interior.ColorIndex = 3
        If Range("H100").Value <> "" Then Range("A100:U100").Interior.ColorIndex = 6
        If Range("N100").Value <> "" Then Range("A100:U100").Interior.ColorIndex = 4
        If Range("U100").Value <> "" Then Range("A100:U100").Interior.ColorIndex = -4142
    End If
End Sub

What do you mean by apply to entire sheet - which part, the change colour or the non blank?

 

[Michele]

the entire code needs to apply to all rows in the spreadsheet. I may have up 1000 entries per year and would like the code to work in every row, not just 100. The spreadsheet is always growing. Did that make sense ?.... tee hee M