Forum Discussion
Conditional Formatting Issue
Hello all,
I have a spreadsheet I use to keep track of paint costs associated with various part numbers we have. I manually enter each line throughout the year. I currently have conditional formatting set to highlight any duplicates within Column A. However, I would now like to set up some sort of conditional formatting formula where Column C is formatted if they are unique values if Column A are duplicates. I have a screenshot to better illustrate what I'm saying. What I'm looking to do is after I had manually entered C997, both C997 and C990 would highlight the pricing discrepancy between the A990 and A997 duplicates.
I feel as though this may be a simple XLOOKUP formula and I'm just overthinking but could use some expertise on how to write up the formula.
Thank you!
Select C3:C997 or however far down you need.
C3 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND(COUNTIF($A$3:$A$997, $A3)>1, COUNTIFS($A$3:$A$997, $A3, $C$3:$C$997, $C3)=1)
Adjust the ranges as needed.
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.
7 Replies
Select C3:C997 or however far down you need.
C3 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND(COUNTIF($A$3:$A$997, $A3)>1, COUNTIFS($A$3:$A$997, $A3, $C$3:$C$997, $C3)=1)
Adjust the ranges as needed.
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.Hello,
I entered your formula but I'm not really understanding what this is doing... I entered my value into A997 and it formatted C990. Then, I entered my value for C997, and it formatted C996. I also tried both "$4.00" and "$3.75" as my value for C997 and it formatted C996 no matter what, even when the prices matched.
In the example below, C991 is what should have ultimately been formatted....
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
