VB conditional formatting problem

[Pauljj]

I have written this code below but no matter whether C4 = V3 or not it always formats it to red (colorindex 3) Can anyone see a glowing mistake of mine please

Private Sub Worksheet_Change(ByVal Target As Range)
Set DayOne = Range("C6:D50")
For Each Cell In DayOne

If Range("C4") <> Range("V3") Then
Cell.Interior.ColorIndex = 1

End If

If Range("C4") = Range("V3") Then
Cell.Interior.ColorIndex = 3

End If

Next

End Sub

 

[Rick Rothstein]

Paul,

The problem appears to be in the formula in V3, specifically, this part of it...

MATCH(T3,T3,0)

which will always return the same value, namely, 1 (the value in T3 will always be found at the first position of the cell T3). Try putting this array-entered** formula in V3 instead and see if that makes things work correctly for you...

=INDEX(C5:P5,,MATCH(T3,TEXT(C5:P5,"dddd"),0))
 
**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

At this point, you can use either your original code or the alternative code I posted for you; if you check back in a little while, I will post the modification I was talking about earlier that restricts the Change event code to executing only when necessary.

 

[Pauljj]

What a legend!!!!!!!

Thank you so much for taking the time to look at this for me, it was driving me made

Thanks once again

Paul

 

[Rick Rothstein]

I guess that means everything is working now... great! Okay, here is the modifications I told you about. This assumes the changes you make for the coloring of the cells is to either C3 or T3. The first version is your code, but tightened up to combine the two individual If..Then blocks into a single If..Then..Else block (and then wrap that in the code to restrict running the Change event to when changes are made in either C3 or T3)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C3" Or Target.Address(0, 0) = "T3" Then
    If Range("C4").Value = Range("V3").Value Then
      Range("C6:D50").Interior.ColorIndex = 3
    Else
      Range("C6:D50").Interior.ColorIndex = 1
    End If
  End If
End Sub

Here is how I would write the Change event code though...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C3" Or Target.Address(0, 0) = "T3" Then
    Range("C6:D50").Interior.ColorIndex = 1 - 2 * (Range("C4").Value = Range("V3").Value)
  End If
End Sub

 

[Pauljj]

once again, many thanks it works perfectly thanks. I would be interested to know how the second code you wrote actually works ? but appreciate you've already spent enough time answering my questions.

Thank you

 

[Rick Rothstein]

This is the key line to look at...

Range("C6:D50").Interior.ColorIndex = 1 - 2 * (Range("C4").Value = Range("V3").Value)

In VB, like in Excel formulas, you can create what is known as a logical expression. All a logical expression is is two constants, variables, etc on either side of a logical operator (=, >, <, etc.). So, this part of the statement is the logical expression...

(Range("C4").Value = Range("V3").Value)[/

It is simply testing value in C4 to see if it equals the value in V3. If it does, the logical expression evaluates to True, otherwise it evaluates to False. In VB, False is numerically equivalent to 0 when used in a mathematical expression (here, I am multiplying the logical expression by 2); however, unlike in Excel formulas where TRUE evaluates to +1 (plus one), in VB, True evaluates to -1 (minus one). So, after we multiply the logical expression by 2, we negate it in order to turn any calculated -2 into a +2. Now, why did we need to do that. Because you wanted ColorIndex values of 1 or 3. So, we start the value we are calculating to assign to the Interior.ColorIndex of C6:D50 with 1 and then subtract 2 times the value returned by the logical expression. What this means is we have either 1+0 or 1+2 being assigned to that ColorIndex depending on if the logical expression is False (the tested values are unequal) or True (the tested values are equal).