Elseif Code Working Partially

[Dokat]

Hi,

[FONT=Verdana, Helvetica, Arial, sans-serif]I have below code that should highlight cells R3, R4, R5 in black. However it is only highlighting R3 and R4 and not R5. Can someone please tell me whats wrong with i.[/FONT]

Private Sub Worksheet_Change(ByVal Target As Range)        Dim R1 As Range, R2 As Range, R6 As Range, R3 As Range, R4 As Range, R5 As Range    Set R1 = Range("CG1")    Set R2 = Range("CF1")    Set R6 = Range("CH1")    Set R3 = Range("E2")    Set R4 = Range("D2")    Set R5 = Range("F2")            If R1.Value = 1 Then        R3.Interior.Color = vbWhite            ElseIf R1.Value = 0 Then        R3.Interior.Color = 6            ElseIf R6.Value = 1 Then        R5.Interior.Color = vbWhite            ElseIf R6.Value = 0 Then        R5.Interior.Color = 6            ElseIf R2.Value = 1 Then        R4.Interior.Color = vbWhite            Else       R4.Interior.Color = 6           End If
End Sub

 

[philwojo]

I am far from a VBA expert, but it appears you are mixing referencing R5 and R6 in this part of your code:

[COLOR=#333333]ElseIf R6.Value = 1 Then
R5.Interior.Color = vbWhite 
           ElseIf R6.Value = 0 Then 
       R5.Interior.Color = 6 [/COLOR]

I think you would need to change those R6's to R5's and test again.

 

[Dokat]

Hi,

Thanks for your reply. That didn't work. R5 needs to change based on the value in R6.

Thanks

 

[philwojo]

So looking at your code in a more readable format I see it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R1 As Range, R2 As Range, R6 As Range, R3 As Range, R4 As Range, R5 As Range    
Set R1 = Range("CG1")    
Set R2 = Range("CF1")    
Set R6 = Range("CH1")    
Set R3 = Range("E2")    
Set R4 = Range("D2")    
Set R5 = Range("F2")            


If R1.Value = 1 Then
        R3.Interior.Color = vbWhite
ElseIf R1.Value = 0 Then
        R3.Interior.Color = 6
ElseIf R6.Value = 1 Then
        R5.Interior.Color = vbWhite
ElseIf R6.Value = 0 Then
        R5.Interior.Color = 6
ElseIf R2.Value = 1 Then
        R4.Interior.Color = vbWhite
Else       R4.Interior.Color = 6
End If


End Sub

It seems to me that you might meeting one of the other tests before you get to the ElseIf to test R6. Again, I'm not an expert at this I might be looking at it wrong and maybe someone else can chime in on it if that is the case.

I think if you want to test all of the ranges for R1, R6 and R2 and then change the interior colors of R3, R5 and R4 respectively that you would need to split out each of those test into it's own If/Then/Else statement. To me, as I read your code it will only ever change one of those ranges, the first one that satisfies the test.