Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range
Hi All,
I require a Conditional Format Formula to Highlight every 3rd Cell in Red that is Offset one column to the Right of the Dynamic Range called POSITION - starting from Row19. Also, is it possible for the Formula to return the number 3 in the highlighted Red Cells? Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
#2
|
|||
|
|||
Assuming that Column I is the column next to your dynamic range, try the
following... Conditional Formatting: a) Select cell I19 b) Format Conditional Formatting Formula Is c) Enter the following formula: =(ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0) *Note that every 3rd cell will be highlighted, starting with the first cell. If you wish to start with the first occurrence of 3rd, change +0 to +1. d) Choose your formatting e) Click Ok f) Copy the formatting to other cells in the Column using the 'Format Painter' or 'Copy Paste Special Formats' Formula: I19, copied down: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0),3,"") Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi All, I require a Conditional Format Formula to Highlight every 3rd Cell in Red that is Offset one column to the Right of the Dynamic Range called POSITION - starting from Row19. Also, is it possible for the Formula to return the number 3 in the highlighted Red Cells? Thanks Sam |
#3
|
|||
|
|||
Hi Domenic,
Thank you very much - that worked Great. I used this as the Conditional Formatting Formula Is: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0),3,"") Thanks Sam Domenic wrote: Assuming that Column I is the column next to your dynamic range, try the following... Conditional Formatting: a) Select cell I19 b) Format Conditional Formatting Formula Is c) Enter the following formula: =(ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0) *Note that every 3rd cell will be highlighted, starting with the first cell. If you wish to start with the first occurrence of 3rd, change +0 to +1. d) Choose your formatting e) Click Ok f) Copy the formatting to other cells in the Column using the 'Format Painter' or 'Copy Paste Special Formats' Formula: I19, copied down: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0),3,"") Hope this helps! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
#4
|
|||
|
|||
Hi Sam,
In article , "Sam via OfficeKB.com" wrote: Thank you very much - that worked Great. You're very welcome! I used this as the Conditional Formatting Formula Is: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0),3,"") Actually, this formula was intended for the second part in which you want the number 3 in every 3rd cell. Enter it in cell I19 and copy down. The first formula would suffice for Conditional Formatting. |
#5
|
|||
|
|||
Hi Domenic,
When I posted reply, I forget to show the Formula using the version that starts with the first occurrence of 3rd, changing +0 to +1. Domenic wrote: *Note that every 3rd cell will be highlighted, starting with the first cell. If you wish to start with the first occurrence of 3rd, change +0 to +1. =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+1,3)=0),3,"") I used the above Formula in the Conditional Formatting Formula Is, to produce the Conditional Format Red Cell Interior and number 3 in the Cells at the same time. Domenic wrote: Actually, this formula was intended for the second part in which you want the number 3 in every 3rd cell. Enter it in cell I19 and copy down. The first formula would suffice for Conditional Formatting. Thanks Sam Domenic wrote: Hi Sam, Thank you very much - that worked Great. You're very welcome! I used this as the Conditional Formatting Formula Is: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+0,3)=0),3,"") Actually, this formula was intended for the second part in which you want the number 3 in every 3rd cell. Enter it in cell I19 and copy down. The first formula would suffice for Conditional Formatting. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
#6
|
|||
|
|||
You mean it works? I didn't think that was possible. It certainly
doesn't work on my version of Excel. Hmmm... Would you mind sending me a sample so that I can have a look at it? In article , "Sam via OfficeKB.com" wrote: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+1,3)=0),3,"") I used the above Formula in the Conditional Formatting Formula Is, to produce the Conditional Format Red Cell Interior and number 3 in the Cells at the same time. |
#7
|
|||
|
|||
Hi Domenic,
You're right, it doesn't work. My eyes and memory are playing tricks on me! I was sure I deleted the Formula in Column I19 down and the number 3 remained by using only the Formula below in the Conditional Formatting Is Formula: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+1,3)=0),3,"") But it doesn't work. Everything stands as it did in your original (first) reply. I'm going to put this one down to sleep deprivation. I don't know what I was looking at! Apologies for mix up. Domenic wrote: You mean it works? I didn't think that was possible. It certainly doesn't work on my version of Excel. Hmmm... Would you mind sending me a sample so that I can have a look at it? =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+1,3)=0),3,"") I used the above Formula in the Conditional Formatting Formula Is, to produce the Conditional Format Red Cell Interior and number 3 in the Cells at the same time. Cheers, Sam Domenic wrote: You mean it works? I didn't think that was possible. It certainly doesn't work on my version of Excel. Hmmm... Would you mind sending me a sample so that I can have a look at it? =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+1,3)=0),3,"") I used the above Formula in the Conditional Formatting Formula Is, to produce the Conditional Format Red Cell Interior and number 3 in the Cells at the same time. -- Message posted via http://www.officekb.com |
#8
|
|||
|
|||
No problem! Thanks Sam!
Cheers! In article , "Sam via OfficeKB.com" wrote: Hi Domenic, You're right, it doesn't work. My eyes and memory are playing tricks on me! I was sure I deleted the Formula in Column I19 down and the number 3 remained by using only the Formula below in the Conditional Formatting Is Formula: =IF((ROWS(I$19:I19)<=ROWS(Position))*(MOD(ROW()-ROW(I$19)+1,3)=0),3,"") But it doesn't work. Everything stands as it did in your original (first) reply. I'm going to put this one down to sleep deprivation. I don't know what I was looking at! Apologies for mix up. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) | |||
Conditional format problem | Excel Discussion (Misc queries) | |||
conditional format of data tables in charts | Charts and Charting in Excel | |||
Copying a conditional format | Excel Worksheet Functions | |||
Conditional Format With SUMIF | Excel Worksheet Functions |