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

 

[Michele]

BTW, the row color change edit you provided works like a champ !!! thanks a bunch !!!

 

[JamesW]

So if the user enters 123 into any row in column B then make all rows Red?

Edit: Off home now, you can either wait until tomorrow, or maybe someone else will be able to help?

 

[Michele]

NO, the way the code is written now looks to me like the only ROW that will perform these functions is ROW 100, I would like every ROW to perform like this.

The data input is put in say ROW 2 then only ROW 2 is affected.
lets say I have 500 items in my sheet, and I want to update ROW 25, then only ROW 25 should be affected. Each row is independant of the others, I am 1 item per ROW A-U, but may have as many as 10,000 items when done.

Is there something I can edit in the code to apply this code to the entire spreadsheet ?

I can't figure out how to do a screen capture to post my spreadsheet for anyone to see.

DARN !!! @#$%&!@ M

 

[Michele]

how do I post my sheet ?

my helper has gone home for the day...... anyone interested ?

 

[JamesW]

Hi there,

Made a few changes:

Private Sub Worksheet_Change(ByVal Target As Range)
    tRow = Target.Row
    tAddress = Target.AddressLocal(False, False)
    
    If Not Intersect(Target, Range("B" & tRow & ",H" & tRow & ",N" & tRow & ",U" & tRow)) Is Nothing Then
        If tAddress = "B" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = 3
        If tAddress = "H" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = 6
        If tAddress = "N" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = 4
        If tAddress = "U" & tRow And Target.Value <> "" Then Range("A" & tRow & ":U" & tRow).Interior.ColorIndex = -4142
    End If
End Sub

Let me know what you think.

 

[jullin2]

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.

it very much helped me! thank you

 

[Michele]

FANTASTICAL !!!!

WOW WOW WOW WOW WOW !!!!!!!!!!!

THANK YOU / THANK YOU / THANK YOU !!!!!!

OMG OMG OMG OMG

This code works really well, I do get an error when I try to Highlight more than 1 coded cell at a time for deleting but I would never do that on a usual basis, so no harm....... this is so fantastic, I am so greatful for the help. I am comparing your previous code to the latest code you provided and trying to understand how the changes you made effect the actions, I am intriqued !!!!

thanks so much, this was AWESOME !!! sorry for the rant but this is GREAT !!!

Thanks and hugggggs XXXX M

 

[JamesW]

lol, the best thankyou post I've ever seen

I can go through what the code is doing if you are interested?

 

[Michele]

Now I went and done it....

If I accidentally double click in a cell with text, that was a previous color, it doesn't realize the step ahead has been activated and reverts back to the previous color ??!! make sense ?

Lets say I have columns A,B,C,D and if I put any text in them, they should change color...and they DO. But if I have progressed through to step C and I accidentally double click in column B, which is step B, the row changes color to specifications in B, thinking that I wanted it to. Is there a way to make the row stay the color of the most recent step ?

say step/column ABC are filled in and I just double click or edit step/column A, if there is text in column B, C or D, the row should indicate the most recent step.

sorry, I have a headache now and thought I would give it to someone else...lol M

 

[Michele]

lol, the best thankyou post I've ever seen

I can go through what the code is doing if you are interested?

yes !!! that would be great.... as soon as I figure out this latest problem I posted about the row colors changing back....lol