Conditional Formatting?

Aeafa

New Member
Joined
Aug 31, 2016
Messages
12
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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?
 
Upvote 0
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?
 
Upvote 0
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()
 
Upvote 0
Solution
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!
 
Upvote 0
I'm glad to help you. Thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top