Changing cell color base on condition

[rubic]

I have table with data from Column A to Column P. I want to create a sub routine that enables to change the background color to "grey" if the entry in Column O is the word "Closed".
Appreciate your help.

 

[jeffreybrown]

How about with conditional formatting...

Highlight Column A - P and in format > conditional formatting > formula Is enter =$O1="Closed"

 

[rubic]

Thanks for the suggestion. As the range of cells are quite large I have already used conditional formatting for other purposes, I think a vba solution would be most fitting.

 

[Sal Paradise]

Who needs VBA?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Binary	Oct	Dec	Hex	Glyph	Binary	Oct	Dec	Hex	Glyph	Binary	Oct	Dec	Hex	Status	Glyph
2	010 0000	40	32	20	?	100 0000	100	64	40	@	110 0000	140	96	60	Open	`
3	010 0001	41	33	21	!	100 0001	101	65	41	A	110 0001	141	97	61	Closed	a
4	010 0010	42	34	22	"	100 0010	102	66	42	B	110 0010	142	98	62	Open	b
5	010 0011	43	35	23	#	100 0011	103	67	43	C	110 0011	143	99	63	Open	c
6	010 0100	44	36	24	$	100 0100	104	68	44	D	110 0100	144	100	64	Open	d
7	010 0101	45	37	25	%	100 0101	105	69	45	E	110 0101	145	101	65	Open	e
8	010 0110	46	38	26	&	100 0110	106	70	46	F	110 0110	146	102	66	Open	f
9	010 0111	47	39	27	'	100 0111	107	71	47	G	110 0111	147	103	67	Open	g
10	010 1000	50	40	28	(	100 1000	110	72	48	H	110 1000	150	104	68	Open	h
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =$O2="Closed"	Abc

 

[Stormseed]

Sub Colours()
Dim i As Integer
For i = 2 To 100
    Select Case Range("O" & i).Value
    Case "Closed": Range("A" & i & ":P" & i).Interior.ColorIndex = 5
    End Select
Next i
End Sub

Enjoy

 

[rubic]

Stormseed,

Thanks for your help. In your code

For i = 2 To 100

is this referring to row 2 to 100?

 

[Stormseed]

Stormseed,

Thanks for your help. In your code

For i = 2 To 100

is this referring to row 2 to 100?

Yes, you are correct