Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default 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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting on cells with a VLOOKUP formula in them JenniM Excel Discussion (Misc queries) 4 April 1st 05 06:45 PM
Conditional format problem PJ Excel Discussion (Misc queries) 2 March 11th 05 03:05 PM
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 04:56 PM
Copying a conditional format Meaux Excel Worksheet Functions 2 November 29th 04 11:19 AM
Conditional Format With SUMIF Minitman Excel Worksheet Functions 3 November 1st 04 03:58 PM


All times are GMT +1. The time now is 02:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"