VBA to highlight row and limit

[ANE0709]

Not sure how to word that but i started writing a change event that highlights the row if certain conditions are met. im still very much learning change events and highlighting using VBA so im not sure how to work this, what i have is a start but probably far off. Basically the conditions i am trying to meet are:

Highlight columns A-AP if:
if the value of column C = C then highlight row green (color 5296274)
if column I <> 0 then highlight yellow (color index 6)
if column I and column L <> 0 then highlight yellow (color index 6)
if column J and column L <> 0 then highlight purple (color index 39)

Should there be a conflict with column C, I, and J i want column C to take priority. This shouldnt ever happen unless there is user error but just in case.

Columns C and I-P are all manual entry by the user.

 

[ANE0709]

My second suggestion didn't work? It should, and it theoretically should work faster than my first suggestion.

i didnt check the second one because the 1st one worked with out any issues and returned almost instantly.

 

[bebo021999]

Solution in #4 and #6 , colour does not update, if cell value editted...
For instant
C4= "C"; I4 = 1 => GREEN
Delete C4 , => GREEN remains, does not change to to yellow.

 

[johnnyL]

@bebo021999 That wasn't asked for by the OP.

That being said, I would have to say your code solution is better than mine.

 

[johnnyL]

That being said, I would have to say your code solution is better than mine.

Well maybe I spoke too soon. Your code has a flaw.

No check for Rows >3 being changed.

Great job though.

 

[bebo021999]

Well maybe I spoke too soon. Your code has a flaw.

No check for Rows >3 being changed.

Great job though.

You 're right.
Not a problem if row 1-3 was header, and no single "C" in C1:C3, and no single value in range I1:L3
BTW, its easy to add row>3 criteria.
something like this:

Case ... AND target.row >3

 

[ANE0709]

Solution in #4 and #6 , colour does not update, if cell value editted...
For instant
C4= "C"; I4 = 1 => GREEN
Delete C4 , => GREEN remains, does not change to to yellow.

You 're right.
Not a problem if row 1-3 was header, and no single "C" in C1:C3, and no single value in range I1:L3
BTW, its easy to add row>3 criteria.
something like this:

Case ... AND target.row >3

i did notice if the C is deleted from C4 the color does not change back. its such a minor detail compared to the larger picture that i wasnt going to mention it. however, if you have a suggestion to correct that i would be glad to add it in.

i dont think i follow what you are referencing to the AND target.row >3. could you elaborate? As it is currently written i havent found any flaws aside from the color if C is deleted.

 

[bebo021999]

from my previous solution, added in line #4

Private Sub Worksheet_Change(ByVal Target As Range)
Dim U As Range
Set U = Union(Range("C:C"), Range("I:L"))
If Intersect(Target, U) Is Nothing or target.row <4 Then Exit Sub ' add
With Range(Cells(Target.Row, "A"), Cells(Target.Row, "P")).Interior
    Select Case True
        Case U(Target.Row, 1) = "C" ' 1st priority
            .Color = 5296274
        Case U(Target.Row, 7) <> 0 '' 2nd priority
            .ColorIndex = 6
        Case U(Target.Row, 8) <> 0 And U(Target.Row, 10) <> 0 ' 3rd priority
            .ColorIndex = 39
        Case Else
            .Color = xlNone 'other
    End Select
End With
End Sub

 

[ANE0709]

You 're right.
Not a problem if row 1-3 was header, and no single "C" in C1:C3, and no single value in range I1:L3
BTW, its easy to add row>3 criteria.
something like this:

Case ... AND target.row >3

ah,, ok. i see what you are saying about AND target.row >3. your assumption is correct. Rows 1-3 are header rows with text and will never change. Actual data starts on row 4. thus far everything is testing pretty smoothly. i finished writing the rest of the macro and plan to have the end users test tomorrow.

@johnnyL you are correct as well. if C is deleted from C4 ideally the color would revert to follow the other criteria set. however, i did not ask for it in the OP because i didnt think about that until i started testing. BUT its such a minor detail compared to the rest of the sheet i wasnt going to mention it.

 

[johnnyL]

@ANE0709, it's all good. @bebo021999 wins this battle.

 

[bebo021999]

ah,, ok. i see what you are saying about AND target.row >3. your assumption is correct. Rows 1-3 are header rows with text and will never change. Actual data starts on row 4. thus far everything is testing pretty smoothly. i finished writing the rest of the macro and plan to have the end users test tomorrow.

@johnnyL you are correct as well. if C is deleted from C4 ideally the color would revert to follow the other criteria set. however, i did not ask for it in the OP because i didnt think about that until i started testing. BUT its such a minor detail compared to the rest of the sheet i wasnt going to mention it.

Have you test my earliest code in #17?
It trigger change from row 4, and update the color automaticaly.