On Change cell Formatting issue

[austinpcherry]

I have a Workbook with 31 Worksheets. On each Worksheet I have 3 columns total (A,B,C) and each entry is two rows. I need help to change the interior color of A1 & A2 (and each pair with the same reference) when a value is entered into the top row (for each pair) of the B Column. I will also remove the color when something goes into the C Column, but I should be able to figure that out from the first change code.

A1 and A2 color to rgbGreen when a value is put into B1, then ColorIndex = 0 when a value is put into C1. Then I need it to continue all the way down the sheet.

I hope that makes sense, and thank you in advance.

 

[RickXL]

Difficult to say without more information.

Where did you place the macro?
Is Workbook_SheetChange2 its real name?
Do you want it to run only on even row numbers?
Do you realise that vbNull is not the same as vbNullString?

 

[austinpcherry]

I put it below the other one you wrote in ThisWorkbook.
Yes, I just put the 2 in there to distinguish them apart.
Yes, only even rows.
I see that there is a difference, but I am foggy on that difference.

 

[RickXL]

The problem is that if you want it to work you will need to use the same name.

If you look at the dropdowns at the top of the edit screen and select Workbook in the left hand one then you will see the available options in the right hand one.

So instead of running two you will need to combine them into one - perhaps like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    With Sh
        If (Target.Row Mod 2) = 1 Then ' Odd Rows
            If Target.Column = 2 Then
                If Target <> vbNullString Then Target.Offset(0, -1).Resize(2).Interior.Color = RGB(0, 255, 0)
            End If
            If Not Intersect(Target, .Columns(3)) Is Nothing Then
                If Target <> vbNullString Then Target.Offset(0, -2).Resize(2).Interior.ColorIndex = -4142
            End If
        Else                            ' Even Rows
            If Target.Column = 5 Then
                If Target <> vbNullString Then
                    Target.Offset(0, 1).Value = Target.Value - Target.Offset(0, -1).Value - Target.Offset(0, 2).Value - Target.Offset(0, 3).Value
                End If
            End If
        End If
    End With
End Sub

I have simplified the column selection as well. "If Not Intersect ... Is Nothing" is a bit over-complicated for what we need.
vbNullString is the value of an empty cell.
vbNull has a value of 1.

I don't know if you need to check for an empty cell or not.

 

[austinpcherry]

Ok, I see. Sorry for the noobness. I am 1 week into learning VBA. Is there a reason that you did not use the target.columns for the column 3 check?

 

[austinpcherry]

I left the "If not intersect" because if it isn't broken then why fix it.
Is there anything wrong with my current code? It all seems to be working.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        If (Target.Row Mod 2) = 1 And Target.Count = 1 Then 'Odd Rows
            If Not Intersect(Target, .Columns(4)) Is Nothing Then
                If Target <> vbNullString Then Target.Offset(0, -3).Resize(2).Interior.Color = rgbLightGreen
            End If
            
            If Not Intersect(Target, .Columns(12)) Is Nothing Then
                If Target <> vbNullString Then Target.Offset(0, -11).Resize(2).Interior.ColorIndex = 0
            End If
        End If 'End Odd Rows
        
        If (Target.Row Mod 2) <> 1 And Target.Count = 1 Then 'Even Rows
            If Not Intersect(Target, .Columns(2)) Is Nothing Then
                If Target <> vbNullString Then
                    Target.Offset(0, 4).Value = Target.Offset(0, 3).Value - Target.Offset(0, 2).Value - Target.Offset(0, 1).Value - Target.Value
                End If
            End If
            
            If Not Intersect(Target, .Columns(11)) Is Nothing Then
                If Target <> vbNullString Then
                    Target.Offset(0, 1).Value = Target.Value - Target.Offset(0, -1).Value - Target.Offset(0, -2).Value - Target.Offset(0, -9).Value
                End If
            End If
            
            If Not Intersect(Target, .Columns(7)) Is Nothing Then
                If Target <> vbNullString Then
                    Target.Offset(-1, 0).Value = Target.Offset(0, -1).Value - Target.Value
                End If
            End If
            
            If Not Intersect(Target, .Columns(13)) Is Nothing Then
                If Target <> vbNullString Then
                    Target.Offset(0, 1).Value = Target.Offset(0, -1).Value - Target.Value
                End If
            End If
        End If ' End Even Rows
    End With
End Sub

 

[RickXL]

Ok, I see. Sorry for the noobness. I am 1 week into learning VBA. Is there a reason that you did not use the target.columns for the column 3 check?

Yes, brain fade coupled with old age

 

[RickXL]

I left the "If not intersect" because if it isn't broken then why fix it.
Is there anything wrong with my current code? It all seems to be working.

Well, if that is what you want it to do and it is doing it then I would say it would seem to be OK. I can see nothing that might cause a problem.

I have to admit that the "if not intersect" is my default method but its workings are not immediately obvious to most people. So in order not to over-complicate things I suggested an alternative - but if you are happy then it is not a problem. It works for more cases than most methods because you can make it detect overlapping ranges and not just coincident ones.

 

[austinpcherry]

I really appreciate your help. Thank you very much. I have learned a lot.

 

[RickXL]

No problem - that is what we are here for ...

Happy New Year