Coloring cells in sheet 2 based on data sheet 1 Excel VBA Solved/Closed

Question

Hi!

I would like to ask a question about using VBA for colouring a cell in sheet 2 based on a value of a cell in sheet 1. I want to write a code in which the cells in sheet 2 automatically change if the specific cell in sheet 1 becomes Yes or No.

In sheet 1 the cell which is changing between Yes and No is C46. In sheet 2 the whole row 416 should change in colour if the answer in sheet 1 C46 is Yes.

I wrote the following code, but it is not working. Can someone help me?

Sub ChangeColor()

Set Myrange = Sheets("Sheet1").Range("C46")

For Each cell In Myrange

Select Case cell.Value

Case Is = "Yes"
    Sheets("Sheet2").Cells("416:416").Interior.ColorIndex = 16
    
Case Is = "No"
    Sheets("Sheet2").Cells("416:416").Interior.ColorIndex = xlNone
    
Case Else

End Select

End Sub

TrowaD · Accepted Answer

Hi Sue-Ellen,

For your code to work, some changes need to be made:

Sub ChangeColor()
Set Myrange = Sheets("Sheet1").Range("C46")
For Each cell In Myrange
    Select Case cell.Value
        Case Is = "Yes"
            Sheets("Sheet2").Rows("416:416").Interior.ColorIndex = 16
        Case Is = "No"
            Sheets("Sheet2").Rows("416:416").Interior.ColorIndex = xlNone
    End Select
Next cell
End Sub

I would do it like this:

Sub RunMe()
If Sheets("Sheet1").Range("C46") = "Yes" Then Sheets("Sheet2").Rows(416).Interior.ColorIndex = 16
If Sheets("Sheet1").Range("C46") = "No" Then Sheets("Sheet2").Rows(416).Interior.ColorIndex = xlNone
End Sub

But this can also be done using Conditional Formatting.

Best regards,
Trowa

TrowaD · Answer

Hi Sue-Ellen,

Sure!

Paste the code below under Sheet1 instead of a standard Module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("C46"), Target) Is Nothing Then Exit Sub
If Target.Value = "Yes" Then Sheets("Sheet2").Rows(416).Interior.ColorIndex = 16
If Target.Value = "No" Then Sheets("Sheet2").Rows(416).Interior.ColorIndex = xlNone
End Sub

Best regards,
Trowa

Coloring cells in sheet 2 based on data sheet 1 Excel VBA

2 responses

Similar discussions

Subscribe To Our Newsletter!