CODEWITHSUNDEEP
vbaexcel
John Green
John Green

Reputation: 1

Excel Macro: How to change the cell color of a row within a range

I have a range from C17:W39. I would like to make the row within the range Grey or White depending on the value in column E. If the volume is Actual, I would like the row to be grey and if the value is guess, I would like the row to be white. I need it so that only the cell color within the range is changed not the entire row. The below code works on the first row but I can't figure out how to make it loop through the rest of the range. Any help would be appreciated. Thanks. 

Sub Color()
    Dim myRange As Range
    Dim cell As Range
    Set myRange = Range("C17:W17")
        For Each cell In myRange
        If cell.Value = "ACTUAL" Then myRange.Interior.ColorIndex = 15
        If cell.Value = "GUESS" Then myRange.Interior.ColorIndex = 0
    Next
End Sub

Upvotes: 0

Views: 3434

Answers (1)

Netloh
Netloh

Reputation: 4378

You could change your code like this:

Sub Color()
    Dim myRange As Range
    Dim cell As Range
    Set myRange = Range("E17:E39")
        For Each cell In myRange
        If cell.Value = "ACTUAL" Then
            Range("C" & cell.Row & ":W" & cell.Row).Interior.ColorIndex = 15
        End If
        If cell.Value = "GUESS" Then
            Range("C" & cell.Row & ":W" & cell.Row).Interior.ColorIndex = 0
        End If
    Next
End Sub

This loops through each cell in range E17:E39 and formats the cells from column C to W in response to the value found in the E-column.

Upvotes: 1

Related Questions