Excel 2002: How to conditional format band of colors ?
 

Hi,

Conditional formating feature in Excel helps to brighten up the worksheet
and makes analysing data a lot easier and less stressful.

May I know how to conditional format repeated bands of colors in the
folowing example ?

Eg 1:
A ... M

1 Green
2 Yellow
3
200


E.g 2
A ... M

1 Green
2 Yellow
3 Red
200


E.g 3
A .... M

1 Green
2 Yellow
3 Red
4 Blue
5
200

Kindly show me the Formula 1, 2, 3 and 4 in the dialog box for each example
and the area needs to be highlighted before formating.


Many Thanks

Low


--
A36B58K641

Excel 2002: How to conditional format band of colors ?
 

You onlyy have 3 conditional formating in excel so you can only have a
maximum of three colors. You can use the MOD function along with ROW to
color rows

=if(MOD(ROW(A1),3)=0,true,false) color 1
=if(MOD(ROW(A1),3)=1,true,false) color 2
=if(MOD(ROW(A1),3)=2,true,false) color 3

Or you can write a macro to color your sheet

Lastrow = range("A" & Rows.count).end(xlup).Row
for rowCount = 1 to Lastrow
Select Case RowCount Mod 4 'number of colors

Case 0 : Rows(RowCount).interior.colorindex = 3
Case 1 : Rows(RowCount).interior.colorindex = 4
Case 2 : Rows(RowCount).interior.colorindex = 5
Case 3 : Rows(RowCount).interior.colorindex = 6
end select
next rowCount

next RowCount




"Mr. Low" wrote:

Hi,

Conditional formating feature in Excel helps to brighten up the worksheet
and makes analysing data a lot easier and less stressful.

May I know how to conditional format repeated bands of colors in the
folowing example ?

Eg 1:
A ... M

1 Green
2 Yellow
3
200


E.g 2
A ... M

1 Green
2 Yellow
3 Red
200


E.g 3
A .... M

1 Green
2 Yellow
3 Red
4 Blue
5
200

Kindly show me the Formula 1, 2, 3 and 4 in the dialog box for each example
and the area needs to be highlighted before formating.


Many Thanks

Low


--
A36B58K641

Excel 2002: How to conditional format band of colors ?
 

Joel's instructions are on the money but if you need some detailed
instructions on condtional formatting the following website has some great
information.

http://www.contextures.com/xlCondFormat03.html
--
Kevin Backmann


"Joel" wrote:

You onlyy have 3 conditional formating in excel so you can only have a
maximum of three colors. You can use the MOD function along with ROW to
color rows

=if(MOD(ROW(A1),3)=0,true,false) color 1
=if(MOD(ROW(A1),3)=1,true,false) color 2
=if(MOD(ROW(A1),3)=2,true,false) color 3

Or you can write a macro to color your sheet

Lastrow = range("A" & Rows.count).end(xlup).Row
for rowCount = 1 to Lastrow
Select Case RowCount Mod 4 'number of colors

Case 0 : Rows(RowCount).interior.colorindex = 3
Case 1 : Rows(RowCount).interior.colorindex = 4
Case 2 : Rows(RowCount).interior.colorindex = 5
Case 3 : Rows(RowCount).interior.colorindex = 6
end select
next rowCount

next RowCount




"Mr. Low" wrote:

Hi,

Conditional formating feature in Excel helps to brighten up the worksheet
and makes analysing data a lot easier and less stressful.

May I know how to conditional format repeated bands of colors in the
folowing example ?

Eg 1:
A ... M

1 Green
2 Yellow
3
200


E.g 2
A ... M

1 Green
2 Yellow
3 Red
200


E.g 3
A .... M

1 Green
2 Yellow
3 Red
4 Blue
5
200

Kindly show me the Formula 1, 2, 3 and 4 in the dialog box for each example
and the area needs to be highlighted before formating.


Many Thanks

Low


--
A36B58K641