Conditional formatting not applying to full specified range.

[TigerCITSol]

Hey everyone,

I'm building a template to be used at work, and I am trying to create a conditional formatting rule that will fill all cells within a certain range when a specific cell, relative to the template position, reads "PAID IN FULL".

When using the formula =$F$7="PAID IN FULL" I have the functionality I am looking for, but it won't work for the copy and pasted iterations of the template.

When I change to =$F7="PAID IN FULL" the fill will only apply to the first column and row of the specified range.

When I change to =F7="PAID IN FULL" the fill will only apply to the first column of the specified range.

My issue is simply that, when I paste a new copy of the template below the old one, I need the cell F7 to update to FX where X is the reference for the new iteration of the template, and then the conditional formatting must also apply to this copy and pasted iteration.

If anybody has any ideas as to why this behaviour is occurring, where I'm going wrong, or how to correct my formula it would be greatly appreciated.




Images attached below this line for reference:

 

[Joe4]

Welcome to the Board!

I have had mixed results with the Format Painter, when it comes to Conditional Formatting.
I find I have much better results if I select all the ranges I want to apply the Conditional Formatting to, and then write the formula as it applies to the first cell in that selection, using the $ where necessary.

If you cannot get that to work, please be more descriptive in showing us exactly what ranges (more than one) you are trying to apply this to, and what the formula should be checking for each range.

 

[TigerCITSol]

Thank you for your reply,

I haven't been using the formatting painter, simply specifying the range either manually by typing it into the conditional formatting popup dialog box, or by selecting all of the cells that I wish the formatting to apply to.

The specific description of the issue is as follows:


I am developing a template for repeated use within the same sheet, and across multiple sheets within the same workbook.

The template is 8 rows by 9 columns. The first iteration would be the range A1:I8 then A9:I16 and so forth.
In the context of the first iteration (A1:I9), the formula should check G7 for the exact text "PAID IN FULL". If it finds this text, it should colour A2:I8 green, as A1:I1 is the headings for the template.

This should be copy and pasteable, so once the template is pasted below in the A9:I16 range, then when G15 reads "PAID IN FULL" A10:I16 should be green.

I have found a temporary workaround since posting this which consists of each individual row checking G7 (or the relative cell for the iteration of the template) and then returning a value in each row from 2:8 and then the conditional formatting checks each row for this value and colours the row green accordingly. This is requiring many more computations and is much less robust than the solution I am looking for, so any further assistance would be appreciated.

 

[Joe4]

OK, I figured out a way to do it with no helper cells.

I set up two different rules. So select columns A:I for all the rows you want to apply these rules to.

Rule 1:
The first one is set up to NOT Conditionally Format any title rows. So it starts with row 1, then jumps to 9, 17, etc.
The formula looks like this:

=MOD(ROW(),8)=1

and choose formatting with "No fill" color

Rule 2:
The second one is set up to Conditionally Format the other rows if the 7th row in each block is "PAID IN FULL".
The formula looks like this:

=INDIRECT("$G"&((INT((ROW()-1)/8))*8)+7)="PAID IN FULL"

and choose formatting with green fill color

And then I set it up to stop at rule 1 if that condition is met.
So this is what the completed Conditional Formatted rules look like: