Previous Topic Next Topic

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

Solved/Closed
Sue-Ellen1994 - Oct 24, 2022 at 10:04 AM
 Sue-Ellen1994 - Nov 4, 2022 at 03:35 AM

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

Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 24, 2022 at 12:05 PM

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
 
2
Sue-Ellen1994
Oct 28, 2022 at 02:25 AM

Hi Trowa,

Thank you very much! Is it also possible to make it an automatically change? For now I have to run the macro, but I want to let the colour change automatically when I change the into yes or no.

Best regards, 

Sue Ellen

0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 3, 2022 at 12:37 PM

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
1
Sue-Ellen1994
Nov 4, 2022 at 03:35 AM

Thank you very much for your help!

1