Forum Discussion
How to highlight a cell if the same number is found on another tab
I am creating a running document that will show the current open POs our company has on one tab and on the second tab is all closed POs. How can I highlight the cells on the first tab if the PO #s corresponds with the updating list on the second tab.
This is a draft document that can bed edited and changed. Would you be able to help assist with the formula?
You can use conditional formatting in Excel to highlight cells on the first tab if the PO numbers match with the list on the second tab.
Here is how you can set it up:
- Open your "Invoice Tracking.xlsx" file and go to the first tab where you have the current open POs.
- Select the range of cells (e.g., the column containing the PO numbers) that you want to apply the formatting to.
- Go to the "Home" tab in the Excel ribbon, click on "Conditional Formatting" in the "Styles" group, and select "New Rule."
- In the "New Formatting Rule" dialog box, choose "Use a formula to determine which cells to format."
- In the "Format values where this formula is true" field, enter the following formula:
=COUNTIF('Closed POs'!$A:$A, A1)>0
Note: Replace 'Closed POs' with the actual name of your second tab, and adjust the column references as necessary.
- Click on the "Format" button to choose the formatting style you want to apply to the cells when the formula evaluates to true. For example, you can select a different fill color to highlight the cells.
- Click "OK" to apply the conditional formatting rule.
Now, any cell on the first tab that matches a PO number in the second tab will be highlighted based on the formatting you specified.
Remember to adjust the formula and cell references to match the actual ranges and tabs in your workbook. The steps were processed with the help of AI.
*The file could not be opened, does not exist.
Hope this will help you.
- NikolinoDE hello, this worked! Thank you! I have now been asked to apply different color schemes based on the PO#s age.
So we would want to use this formula that you created, but add the conditional formatting that if column F on tab DA Invoice Tracking equals 1, 2, 3, 4, 5, 6, they would highlight green. Then create separate conditional formatting to highlight ones at 7 8 9 in yellow, and 10, 11, 12, 13, 14 in red, and then 15 and above would be a deep red. Any suggestions on preventing the workbook from closing when applying this?
https://essexpropertytrust.sharepoint.com/:x:/r/sites/CS/All%20Specialists/PNW%20Master/Deposit%20Accounting/Reports/12.7.23%20-%20Invoice%20Tracking%20Documents/Q1%202024%20-%20Working.xlsx?d=wdbf3a94766eb41ccb0b6cd4d01745f0c&csf=1&web=1&e=0y8HxrI'm glad to hear that the initial solution worked for you! For the additional requirement of applying different color schemes based on the age of the POs, you can use a combination of formulas and conditional formatting rules. It's a good idea to break down the rules into smaller sets to prevent performance issues.
Here's how you can set up conditional formatting for different age groups:
1. Green (1-6 days):
- Select the range of cells (e.g., the column containing the PO numbers) where you want this formatting to apply.
- Create a new conditional formatting rule using the formula:
=AND(COUNTIF('Closed POs'!$A:$A, A1)>0, 'DA Invoice Tracking'!F1>=1, 'DA Invoice Tracking'!F1<=6)
- Choose the green formatting style.
2. Yellow (7-9 days):
- Repeat the above steps with the formula:
=AND(COUNTIF('Closed POs'!$A:$A, A1)>0, 'DA Invoice Tracking'!F1>=7, 'DA Invoice Tracking'!F1<=9)
- Choose the yellow formatting style.
3. Red (10-14 days):
- Repeat the above steps with the formula:
=AND(COUNTIF('Closed POs'!$A:$A, A1)>0, 'DA Invoice Tracking'!F1>=10, 'DA Invoice Tracking'!F1<=14)
- Choose the red formatting style.
4. Deep Red (15 days and above):
- Repeat the above steps with the formula:
=AND(COUNTIF('Closed POs'!$A:$A, A1)>0, 'DA Invoice Tracking'!F1>=15)
- Choose the deep red formatting style.
Regarding your concern about preventing the workbook from closing during the application of these rules, Excel should handle this without any issues. Conditional formatting rules are typically applied quickly unless you have an extremely large dataset.
As for the link you provided, unfortunately, I cannot access external links or SharePoint content directly for my own security reasons.
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
