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 co...
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=0y8Hxr
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=0y8Hxr
I'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.
Hi there, thanks for all your useful information. I have tried to use your formula but for some reason it is not working for me. Hoping you can help!
I have two sheets, First called "Routing" has a column starting at A4 which has numbers entered into it (location numbers), on the other tab "Select" starting A2 is a list of selected locations from our primary list of all locations.
I want to condition the cells in "Routing" tab column A4+ to highlight if that same location number appears in "Select" tab column A
Thank you so much in advance 🙂
To highlight cells in the "Routing" tab column starting at A4 based on whether the same location number appears in the "Select" tab column A, you can use conditional formatting with a formula. Here's how you can set it up:
1. Go to the "Routing" tab.
2. Select the range of cells in column A starting from A4 where you want the conditional formatting to apply.
3. Go to the "Home" tab on the Excel ribbon.
4. Click on "Conditional Formatting" in the "Styles" group, then select "New Rule" from the dropdown menu.
5. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
6. Enter the following formula in the formula field:
=COUNTIF(Select!$A:$A, A4)>0
This formula checks if the value in the current cell (in the "Routing" tab) exists in column A of the "Select" tab.
7. Click on the "Format" button to choose the formatting style you want to apply to the highlighted cells.
8. After selecting the formatting, click "OK" to apply the conditional formatting rule.
9. Click "OK" again to close the "New Formatting Rule" dialog box.
Now, the cells in column A of the "Routing" tab, starting from A4, will be highlighted if the same location number appears in column A of the "Select" tab.
Make sure that the references to the "Select" tab and the ranges are correct in the formula. If your Excel file is in SharePoint, you should be able to access it and apply the conditional formatting as described above. If you encounter any issues, feel free to ask for further assistance!
