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...
- 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)
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)
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.- Thanks,
When I try that, the cells are all highlighted yellow regardless of what's inside of them (or pink if I put that rule higher on the list), I tried it both with the $ or without it.
Some other things that may or may not be pertinent?
- Some of the values produced in the cells by the cells' formulas are negative, but they are all integers.
- My Excel is set up to use the European style with semi-colons instead of commas, which I think you already figured out.
- The cells I'm trying to highlight are in columns AQ through BN; each column's formulas start in row 3 (rows 1–2 are headers for each column).
- The cell formulas in each column reference two different other columns: one of AK through AP and DI, but all of the cells in each column reference the same one of AK through AP. (Why a particular cell has one of the three different formulas in it comes from external data and isn't referenced in the sheet.)
- Each column right now has its own set of conditional formatting in the rules, and their effects are limited to that column (ex. the highlighting that talks about the formulas in column AZ have "=$AZ$3:$AZ$1048576" in the "Applies to" section of the Conditional Formatting Rules Manager), but only because given everything else, I thought I had to limit them this way.
Thanks again for all your help.
