mod(row() change font colour as required

[ozbeachbum]

I have searched the net an Mr Excel and I have come to the conclusion that what I am trying to achieve is not possible unless I use VBA and I don't wish to do so at the moment.

My current formula in conditional formatting is:
mod(row(),4)=3 fill colour is "Tan", Text colour is "ROYAL"
If the cell has "Y" entered I would like to retain the fill colour, but have the text colour change to "GREEN" and if the cell has "N" entered text would be "RED".

Any information would be greatly appreciated, including that it is not possible with a formula if that is the case.

Cheers,
Dave.

 

[dreid1011]

So then you make two more CF rules. Something like this perhaps:

=cell="Y" with formatting: fill color "tan" font color "green"
=cell="N" with formatting: fill color "tan" font color "red"

Where "cell" is the top left cell of your affected range.

 

[ozbeachbum]

So then you make two more CF rules. Something like this perhaps:

=cell="Y" with formatting: fill color "tan" font color "green"
=cell="N" with formatting: fill color "tan" font color "red"

Where "cell" is the top left cell of your affected range.

Hi, thanks so much for the reply.
If I apply the formula as you have suggested, it also changes the lines that are currently NO FILL to tan.

I need to be able to join the mod(row() with the cells that it changes to tan.

The following did not work, but I show it here to demonstrate what I was thinking.
=AND(MOD(ROW(),4)=3,CW11=Y) "fill tan" "font green"

I would appreciate your thoughts, hoping this may explain the position better.

 

[dreid1011]

When you check for text values, you need to use quotes around the value. Did you retype the formula and forget the quotes or did you not use them at all?

=AND(MOD(ROW(),4)=3,CW11="Y")

 

[Peter_SSs]

Spelling it out in a bit more detail, the three rules might look something like this.

24 01 24.xlsm
	B
2	Data 1
3	Data 2
4	Data 3
5	Data 4
6	Data 5
7	Y
8	Y
9	N
10	N
11	N
12
13	Data 12
14	Data 13
15
16	Data 15
17
18	Data 17
19	N
20	Data 19
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B20	Expression	=AND(MOD(ROW(),4)=3,B2="N")	text	NO
B2:B20	Expression	=AND(MOD(ROW(),4)=3,B2="Y")	text	NO
B2:B20	Expression	=MOD(ROW(),4)=3	text	NO

 

[ozbeachbum]

When you check for text values, you need to use quotes around the value. Did you retype the formula and forget the quotes or did you not use them at all?

=AND(MOD(ROW(),4)=3,CW11="Y")

Thanks.
Just forgot, everything works fine.
Appreciate your help.

 

[ozbeachbum]

Spelling it out in a bit more detail, the three rules might look something like this.

24 01 24.xlsm
	B
2	Data 1
3	Data 2
4	Data 3
5	Data 4
6	Data 5
7	Y
8	Y
9	N
10	N
11	N
12
13	Data 12
14	Data 13
15
16	Data 15
17
18	Data 17
19	N
20	Data 19
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B20	Expression	=AND(MOD(ROW(),4)=3,B2="N")	text	NO
B2:B20	Expression	=AND(MOD(ROW(),4)=3,B2="Y")	text	NO
B2:B20	Expression	=MOD(ROW(),4)=3	text	NO

Hi Peter,
Thanks as always.

 

[Peter_SSs]

Hi Peter,
Thanks as always.

You are welcome.

If you would like to mark a solution, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. (I have removed the 'Mark as solution' from your feedback post 6.)