formula in conditional formatting

[freeriding]

Hallo! I set conditional formatting in the cells in red.

The problem is that, when the formula in the conditional formatting is not true (i set the number 5 in cell A94, so it is not blank), then only the first row of the 3 gets white (while all 3 of the rows whould be white, since the conditional formatting applies to A94:C96)

This is solved if in the formula is set $A$94 instead of $A94, but this is not convenient for me in other functions.

Is there another solution?


PS. to make it more clear:

I want that when A1 is blank, then A1:C3 is colored.

When A1 has a number, then A1:C3 is non color filled.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide links to the other site(s) where you have asked this question.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[freeriding]

conditional formatting

Hallo! I set conditional formatting in the cells in red. 734176 The problem is that, when the formula in the conditional formatting is not true (i set the number 5 in cell A94, so it is not blank), then only the first row of the 3 gets white (while all 3 of the rows whould be white, since...

www.excelforum.com

 

[etaf]

you have to fix the cell with $
conditional formatting (CF) will
lets say you select the range A1 to C3
then CF will look at cell A1 and test
then it moves over to cell B1 against B1 then C1 and tests against C1 then down the rows
then it moves over to cell A2 against A2 then B2 and tests against B2etc

now by fixing the row A$1
It will check A1 with A1 , B1 with B1 , C1 with C1
then A2 with A1 , B2 with B1 , C2 with C1 - Because the $ fixes the row to always check row 1

Now by fixing the Column with $A$1
It will check A1 with A1 , B1 with A1 , C1 with A1
then A2 with A1 , B2 with A1 , C2 with A1 - Because the $ fixes the row & Column to always check row 1

BUT if in Conditional formatting you do not out the absolute reference ie $ then it will change

I think you may need VBA to fix the position of the lookup cell

WHY

but this is not convenient for me in other functions.

The $ are only set within the conditional format formula NOT needed in other functions

can you expand what the issue is

 

[etaf]

Just read your cross post, and already been asked , so i will leave it with the other thread as that has progressed a lot further and awaiting updates

 

[freeriding]

any input?