More Than 3 Conditional Formating

Michele

Board Regular
Joined
Jul 22, 2002
Messages
233
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 :)
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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

Board Regular
Joined
Jul 22, 2002
Messages
233
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

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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

Well-known Member
Joined
Oct 30, 2009
Messages
1,197

ADVERTISEMENT

If all else fails, try:

Code:
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

Board Regular
Joined
Jul 22, 2002
Messages
233
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

Well-known Member
Joined
Oct 30, 2009
Messages
1,197

ADVERTISEMENT

Hi,

Change
Code:
If Not Intersect(Target, Range("B1,H1,N1,U1")) Is Nothing Then
to
Code:
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

Board Regular
Joined
Jul 22, 2002
Messages
233
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

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Try:

Code:
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

Board Regular
Joined
Jul 22, 2002
Messages
233
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 :LOL: M
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,849
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top