# formula in conditional formatting

#### freeriding

##### New Member
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.

### 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
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.

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

#### etaf

##### Well-known Member
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

any input?

Replies
4
Views
129
Replies
7
Views
691
Replies
3
Views
241
Replies
9
Views
356
Replies
3
Views
85

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.

### Which adblocker are you using?

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

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