formattingms office
I need to highlight duplicates in column A, but only if column B is equal to 10.
67890 10
67891 10
67891 20
67891 30
67892 10
67892 20
67894 10
67895 10
67891
In this example, what I want it to do is, as soon as I input the value on column A the cell automatically changes colour.
I have tried using sumproduct and countifs on conditional formatting, but it does not highlight the values.
Thank you in advance.
Found it! It's a setting under Preferences > Edit:
Extend data range formats and formulas
Misread it as "date ranges" on the first trawl through preferences. Turning it off stops this happening.
The comment about formulas seems to only mean that it doesn't fill empty cells with formulas when it thinks it's seen a pattern. After turning it off, it still does other automatic format adjustment e.g. incrementing formulas copied across a range:
Here is a simple one which will require you to
Best Answer
I found a solution; it's not "pretty", but it works. Unfortunately, it is a bit hard to describe, so I'll make an example file with explanations available, instead. In short, it uses
use a formula to determine which cells to formatConditional Formatting option to then color the original cells