Conditional formatting issue

[Sandman1985]

Hi All,

Hope you're well.

Using a formula to conditional format a range and can't for the life of me work out why it isn't working.
I have a value in H9 that is dynamic. I have set the formula to
H9=Sam - taken the absolute reference off the H9 as I want to be able to copy and make the table bigger without continuing to look at the value in H9.
G15:L24 turns red - only G15 is turning red. Not the rest of the specified range.
If I make H9 absolute, it highlights the whole range - but then I can't copy the table over.
Any ideas appreciated

Regards,

Sandman

 

[Joe4]

H9=Sam - taken the absolute reference off the H9 as I want to be able to copy and make the table bigger without continuing to look at the value in H9.
G15:L24 turns red - only G15 is turning red. Not the rest of the specified range.
If I make H9 absolute, it highlights the whole range - but then I can't copy the table over.

I am not quite sure I follow exactly. If you want the entire range G15:L24 to be Conditionally Formatted based on the value in cell H9, you would definitey need to make that range absolute ($H$9), or else it will change.

Can you explain more about copying this table over and expanding it?
Where exactly are you copying it (what is the range address)?
After copying, do you still want to keep the original copied part Conditional Formatted, just not the new parts?
How exactly is this table expanding (just rows, or rows and columns)?

It might be best if you could show us an example of what you are trying to do.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Anthony47]

So (1) you have a Formula that expands its results into a variable area.
Now (2) you would like to conditionally format the whole expanded area using a formula

If the above is correct then my approach would be:
-supposing that the Formula is in R3
-supposing that the area reserved for the expanded area is R3:R20
-Now select R3:R20 and apply a conditional format using a formula like this:

=AND(R3<>"",YourCurrentFormula)

for example:

=AND(R3<>"",R3>4)

This will highlight the values in the expanded area that are higher than 5

If my understanding is not correct then it would be better if you clarify better your question

This is in addition to what already suggested by Joe, above

 

[Sandman1985]

So (1) you have a Formula that expands its results into a variable area.
Now (2) you would like to conditionally format the whole expanded area using a formula

If the above is correct then my approach would be:
-supposing that the Formula is in R3
-supposing that the area reserved for the expanded area is R3:R20
-Now select R3:R20 and apply a conditional format using a formula like this:

=AND(R3<>"",YourCurrentFormula)

for example:

=AND(R3<>"",R3>4)

This will highlight the values in the expanded area that are higher than 5

If my understanding is not correct then it would be better if you clarify better your question

This is in addition to what already suggested by Joe, above

Hi Anthony47, you understood my gibberish. Thank you, that worked well.

Appreciate your help.

 

[Anthony47]

Thank you for the feedback