change colors in a range of excel cells based on another range of cells

B

Bobbi Muck

I would like to change the color of a cell in a range (A5:D50)if any
cell equals any cell in another range (V9:V20). All ranges are dates
and V9:V20 are not necessarily consecutive dates.

In addition that same set of cells A5:D50 would also be compared to a
second set of dates (V30:V35) and if any cell equals any date in this
second range. If so, the cell would change to a different color.

Any ideas?


Thanks,

Bobbi

*** Sent via Developersdex http://www.developersdex.com ***
 
L

Lee Li Fong

Bobbi

Try apply these two formula to conditional formattin
=SUMPRODUCT(--(A5=$V$30:$V$35))>0 (set red colour) and =SUMPRODUCT(--(A5=$V$9:$V$20))>0 (set blue colour) to cell A5. Then copy the cell A5 and paste special, format to other cell A5:D50

Lee Li Fon
Blog: http://smartusemicrosoftoffice.blogspot.com/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lastrow of a range 3
Change the color of a range of cells. 6
Code to add comments to selected cells within a selected range 30
Reposition Cells in a Column based on another 2
Sum a range of cells based on relative position 3
Duplicating worksheet based on range and changing worksheet name 3
Sort a range of strings using VBA code? 5
Copy range based on input 1

Top