Conditional Formatting?

Aeafa

New Member
Joined
Aug 31, 2016
Messages
9
Hi everyone!

I need to make certain cells on a report highlighted based on 2 conditions from raw data.
Basically what I need is:
If the date in range V2-V400 is today, AND the number in range Y2-Y400 is the same as say A2, then I need A2 to be highlighted.

I've been told by a colleague that conditional formatting is the best option, but they couldn't help me with a formula.
And I have never used conditional formatting let alone with 2 conditions that need to be met.

Please can anyone help me out?
Thank you so much in advance!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,098
Office Version
365
Platform
Windows
If the date in range V2-V400 is today,
Are you trying to evaluate each row separately, or are you looking for today's date in any one cell, or ALL the cells?

the number in range Y2-Y400 is the same as say A2
Are you comparing every row in column Y to cell A2, or every row to column A of the same row?

I need A2 to be highlighted.
Only cell A2, or every row in column A that meets the requirements?
 

Aeafa

New Member
Joined
Aug 31, 2016
Messages
9
Thanks for replying!
Sorry I realise I didn't explain it very well.
I work in a hotel, so my report looks like this:

A B
1 Room | Name |
2 501
3 502
4 503

I need A1 to be highlighted only if that room number is departing on today's date.
My data comes from another sheet which the departing date is in column V and the room number is in column Y.
So I need to scan both columns for matches pertaining to each room number. So if for example 'sheet3'!Y5 is the same as 'sheet1'!A1 and 'sheet3'!V5 is the same as today's date, then A1 should be highlighted.
Then repeat the scan and match process for A2, and then A3, etc etc.

I hope that makes sense?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,201
Office Version
2007
Platform
Windows
Try this

On sheet3 create a Range Name "dates" for the dates for the range V2:V400.
Create a Range Name for rooms for the range Y2:Y400

Use the following formula for conditional formatting

=INDEX(dates,MATCH(A2,rooms,0))=TODAY()
 

Aeafa

New Member
Joined
Aug 31, 2016
Messages
9
Omg. I didn't even think of using INDEX! :banghead:

Thank you so so so so much!
You are an absolute life saver. :pray:

I appreciate your help, so much!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,201
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.:)
 

Forum statistics

Threads
1,089,560
Messages
5,408,981
Members
403,247
Latest member
prabutr28

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top