Advanced Conditional Formatting problem

[Ramakanth]

Hi,

I am trying to use conditional formatting to highlight a cell range based on values in another sheet. I am attaching a model sheet for reference. So in this case I want to highlight each day of the 10 weeks based on the values in cell next to corresponding date in Sheet2. In this example I was able to highlight "Sun 20 Apr" based on the value in cell C29 of Sheet2. But this formula cannot be used for all the cell as Ill have to write this formula 70 times. And if I have three colors for three numbers (say blue, red and green corresponding to 1, 2 and 3) how do I simplify further.

kindly help me with this. In case of further clarifications do not hesitate to ask.

 

[SirJB7]

Hi, Ramakanth!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at this file:
https://dl.dropboxusercontent.com/u...ced query (for Ramakanth at chandoo.org).xlsx

It has a few changes as:

1) Worksheet Dashboard, range E5:K14:

a) E5: =$D$4+(FILA()-5)*7+COLUMNA() -----> in english: =$D$4+(ROW()-5)*7+COLUMN()
with format: "ddd*dd mmm", unquoted and replacing "*" by Alt-010

b) conditional format condition 2 (today):
=HOY() -----> in english: =TODAY()

c) CF 1 (for blue values):
=BUSCARV(E5;Sheet2!$A:$C;3;FALSO)=1 -----> in english: =VLOOKUP(E5,Sheet2!$A:$C,3,FALSE)=1

2) Worksheet Sheet2:
A2: =A$1+FILA() -----> in english: =A$1+ROW()
and copy down as required (you can delete column B and if you do so then change the column of VLOOKUP function to 2.

Just advise if any issue.

Regards!

 

[Ramakanth]

Hi SirJB7,

Your solution works perfectly. Thank you very much. I tired to look for cell formatting that takes care of dates in a better way but couldn't find Alt-010. Thanks for that too.

Cheers!!

 

[SirJB7]

Hi, Ramakanth!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Alt-010 is equivalent to Alt-Enter (or vbLF in VBA).