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

Question

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

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!!
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!!

I tried it by writing a separate check for each column and then calling them sequentially in a separate function...which does not seem to be a good idea!! — Amit Ojha
– Amit Ojha, Commented Nov 26, 2012 at 17:13

PaulStock · Accepted Answer · 2012-11-26 17:44:20Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related