Forum Discussion
Solved
Conditional Formatting based on formula
Hi everyone,
I hope you can help this novice Excel user. 🙂
I'm using Office Home and Business 2019 on a Windows 10 PC.
I have cells with three kinds of formulas in it:
=AK3
=SUM(AK3;DI3)
=SUM(AK3;(DI3*2))
What I'd like to do is use conditional formatting to highlight cells of types 2 & 3 in different colors, respectively. This is honestly my first time using conditional formatting and I c
- The easiest way to do this would be to compare the cell value to the AK3 value as your conditional formatting formula. I'm sure they aren't all literally =AK3 but if you are looking at conditional formatting of column AM and AM3 might be one of those 3 options and AM4 would be one of those options but using AK4 and so on down the column then:
Select column AM
Create new rule
Select custom formula option
use =AM1<>AK1 (note if you select only a range in column AM like AM3:AM100 then you must use the first cell accordingly like =AM3<>AK3)
6 Replies
- The easiest way to do this would be to compare the cell value to the AK3 value as your conditional formatting formula. I'm sure they aren't all literally =AK3 but if you are looking at conditional formatting of column AM and AM3 might be one of those 3 options and AM4 would be one of those options but using AK4 and so on down the column then:
Select column AM
Create new rule
Select custom formula option
use =AM1<>AK1 (note if you select only a range in column AM like AM3:AM100 then you must use the first cell accordingly like =AM3<>AK3)- Thank you for helping, especially since my post was unclear and incomplete. 😄
You're right that there are a lot of different cells references and <> worked for one color. But I need to use a second color for the second type of formula.
The formulas start at cell 3.
Example:
In column AZ, there are three different formulas:
=AZ[Row] -- not highlighted
=SUM(AZ[Row];DI[Row]) -- highlighted yellow (solved with your solution)
=SUM(AZ[Row];(DI[Row]*2)) -- highlighted pink (this is the one I'm still having trouble with)
I tried =AZ3>(AK3+AZ3) but that doesn't work. Do you have any suggestions for this?- I thought BOTH of the other 2 needed to be highlighted using the same rule. I you need different colors then use the corresponding formulas:
Yellow: =SUM($AZ3;$DI3)
Pink: =SUM($AZ3;($DI3*2))
Again, this assumes the range you are going to apply it to (what you highlighted before clicking new rule or how you set it in the conditional formatting window) starts on row 3. I added the "$" before each of the column references just in case you are applying it across multiple columns.
