Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colors and conditional format - greater than, less than | Excel Discussion (Misc queries) | |||
Excel 2002 : How to conditional format the Sub Total Table ? | Excel Discussion (Misc queries) | |||
Excel 2002: How to conditional format a row of cells ? | Excel Discussion (Misc queries) | |||
Cell colors on conditional format | Excel Discussion (Misc queries) | |||
How to carry over conditional format colors to a chart | Charts and Charting in Excel |