CODEWITHSUNDEEP
excelvba
Amit Ojha
Amit Ojha

Reputation: 39

Coloring multiple cells of an excel sheet based on the value of a particular cell

I've an excel sheet...where I want to color the cells on the basis of following condition:

  1. If Cell E is non-empty then cell A, Cell B, Cell C, Cell D, Cell F, Cell G, Cell H and Cell I can not be empty. If any one of the cell is found empty then color the respective cell in red!!

  2. If collectively all the cells Cell A, Cell B, Cell C, Cell D, Cell F, Cell G, Cell H and Cell I contains a value and Cell E is empty then highlight the Cell E in red color.

Note: Want to implement this in module section...so suggest the solution accordingly!!

Upvotes: 1

Views: 655

Answers (1)

PaulStock
PaulStock

Reputation: 11263

Would something like this work?

Dim Row
Row = 3

If Not IsEmpty(Cells(Row, 5)) Then
    ' if Cell E is not empty, then highlight empty cols A-I
    For chkcol = 1 To 9
        If chkcol <> 5 Then ' ignore column E
            If IsEmpty(Cells(Row, chkcol)) Then
                Cells(Row, chkcol).Interior.ColorIndex = 3
            End If
        End If
    Next
Else
    blnvalid = True
    ' check for cell E being empty and all others not
    For chkcol = 1 To 9
        If chkcol <> 5 Then
            If IsEmpty(Cells(Row, chkcol)) Then
                blnvalid = False
                Exit For
            End If
        End If
    Next
    If blnvalid Then
        Cells(Row, 5).Interior.ColorIndex = 3
    End If
End If

Upvotes: 1

Related Questions