Aug 19 2021 02:44 PM
Aug 19 2021 03:02 PM
SolutionSelect column B on Sheet2. Cell B1 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(MATCH(B1, 'Sheet 1'!B:B, 0))
Substitute the real name of Sheet 1.
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Jun 06 2022 06:43 AM
@Hans Vogelaar This worked perfect, even though numerous other sites said it was impossible. THANK YOU!
Jun 02 2023 03:03 PM
Nov 07 2023 10:19 AM - edited Nov 07 2023 10:51 AM
@Hans Vogelaar is there a way to highlight only if it matches two columns?
Currently working on an excel tool for work (logistics), and am trying to get our internal list of preferred origin zip & destination zip to auto-highlight on bid data so it is easy for us to identify our focus.
I have two conditional formatting rules set up, but I would like to combine them into one rule and only highlight if both origin & destination match our master file.
Ideally, I'd be able to highlight any zip codes that are within a given radius of our internal data. Let me know if you have any work arounds or ideas!
Nov 07 2023 03:45 PM
How do you determine the distance?
Nov 07 2023 03:50 PM
Nov 08 2023 03:05 AM
Do you have that information in your workbook? If so, 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?
Nov 08 2023 05:44 AM
@Hans Vogelaar I've linked the document. Was discussing with my boss and he thinks a VLookUp between two separate workbooks would work, but he's unsure how to set that up.
Open to any suggestions you have & appreciate the assistance!
Nov 08 2023 01:31 PM
I cannot access that file. You'll have to share it.
Nov 08 2023 01:34 PM
Nov 08 2023 01:35 PM
Click on my user picture and send me a private message. You should be able to attach the workbook there.
Mar 07 2024 11:47 AM
@Hans Vogelaar I'm trying to do something similar. I have multiple sheets in a workbook where I add a new sheet each month.
Column headers are the same each month, but the data changes. I have names (Last, First) in Column A (primary account holder) and sometimes in Column B (their spouse, if applicable) on each sheet.
I need to highlight names on the current month's sheet that have appeared within the previous 11 sheets (1 year of rolling data). Each tab name is the Month and Year (Feb 2024, Mar 2024, etc.) the data applies to.
I can substitute "=ISNUMBER" for "=ISTEXT" in the formula you provided, but I don't know how to have the formula apply to X number of other sheets in the workbook.
Mar 07 2024 01:30 PM
I'll have to leave that to cleverer persons.
@Peter Bartholomew @djclements @Sergei Baklan @L z. @Patrick2788
Mar 08 2024 12:33 AM
Current month with Cond. Format. rule applied to column A:
A Power Query based option. With:
- Monthly sheets formatted as Tables
- Tables named MMM_YYYY (Nov_2023, Dec_2023, Jan_2024...)
- A Helper Sheet (to hide) where a Power Query table list names found in previous Months
- A Defined Name (RangeNamesInPreviousMonths) that refers to the above table
On current month Table, Cond. Format. rule based on formula:
=ISNUMBER( MATCH($A2, RangeNamesInPreviousMonths, 0) )
Mar 11 2024 05:23 AM
@L z. thank you. I'll work on this and report back once I can.
Mar 11 2024 06:09 AM
Attached is the same "re-packaged" as a single query => easier to adapt
Aug 19 2021 03:02 PM
SolutionSelect column B on Sheet2. Cell B1 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(MATCH(B1, 'Sheet 1'!B:B, 0))
Substitute the real name of Sheet 1.
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.