formula in conditional formatting

freeriding

New Member
Joined
Sep 21, 2015
Messages
21
Hallo! I set conditional formatting in the cells in red.

Untitled.jpg


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)

Untitled2.jpg


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.

1622112801543.png
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,545
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2015
Messages
21
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,894
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,894
Office Version
  1. 365
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,141,618
Messages
5,707,427
Members
421,509
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top