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]

Hi,

Whereabouts is "the same reference"?

I presume it is some sort of key but is it in A1, A2 or B1?

Where are the other references that it could match? Are they on the same sheet but lower down or on different sheets or both?

I presume you want this to work for all 31 worksheets?

 

[austinpcherry]

There is no match. It is when any value is put into B1 as well as any value is put into C1. Those two cells are going to hold the Time that the information is entered into A1 and A2.

Then I want the same thing to happen when a time is put into the b3 and c3 cells, ect...
Yes, I would like it to work on all worksheets.

 

[RickXL]

OK, put this code into the module for ThisWorkbook:

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
            If Not Intersect(Target, .Columns(2)) Is Nothing 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
        End If
    End With
End Sub

When a worksheet is changed it triggers the code and tells it which Sheet was changed and what the target range was.
If column B on an odd row is changed and something is entered then the two column A cells are turned green.
If column C on an odd row is changed and something is entered then the two column A cells are turned clear.

 

[austinpcherry]

I am getting a run-time "1004" error on line

Target.Offset(0, -1).Resize(2).Interior.Color = RGB(0, 255, 0)

when I change the value in column B. I also get the same error in the same section of code when I change the value in column C.

 

[austinpcherry]

So........ You get that error if you haven't unlocked the worksheet. Sorry, your code works great. Now to reverse engineer it so I will know for next time.

 

[RickXL]

Sorry, my version does not do that. So we need to find out what you have done differently to me.

I have created 31 worksheets and pasted the code into the ThisWorkbook Module.
Then I can change any value in column 2 or 3 on an odd-numbered row and the formatting will change.

Are you still using columns 2 and 3?

If you change column 3 the error should be on a different line of code. Is that the case?

 

[austinpcherry]

It is working fine Rick. I just had the cells protected and the error was when the code was trying to change the formatting of a protected cell. I unprotected the sheet and it works great.

 

[RickXL]

Thanks for the feedback, I am pleased you got it working.

Regards,

 

[austinpcherry]

Perhaps you can tell me what I am doing wrong here:

Private Sub Workbook_SheetChange2(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        If (Target.Row Mod 2) <> 1 And Target.Count = 1 Then
            If Not Intersect(Target, .Columns(5)) Is Nothing Then
                If Target <> vbNull 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