Previous Topic Next Topic

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

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

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

2 responses

Réponse 1 / 2
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
24 Oct 2022 à 12:05

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
 
Sue-Ellen1994
28 Oct 2022 à 02:25

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

Réponse 2 / 2
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
3 Nov 2022 à 12:37

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
Sue-Ellen1994
4 Nov 2022 à 03:35

Thank you very much for your help!

Similar discussions

Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses
Extract data from one sheet to another
phanikr -
Mazzaropi -

1 response