Conditional Formatting Based on Value of Another Cell

[katkth7533]

Hello again everyone! I was hoping that someone could help me with a formula. I haven't used Excel in a while so I am very rusty and in a time crunch so I am again asking for some help. In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date. How would I do this? Any suggestions? Thank you for your help!

[sktneer]

Try this....
Select A1 and make a New Rule for conditional formatting using the formula given below and set format as per your choice.

Please Login or Register  to view this content.

[katkth7533]

Thank you Skt! Do you know if you an apply conditional formating on a cell if a date is between a range? For example, A1 has text. I want A1 to turn grey is B1 has a date between 1/1/14 and 2/1/14? Thanks

[sktneer]

Try this rule for conditional formatting........

Please Login or Register  to view this content.

[Tony Valko]

I want A1 to turn grey is B1 has a date between 1/1/14 and 2/1/14? Thanks

One way...

=B1=MEDIAN(B1,41640,41671)

41640 is the date serial number for 1/1/2014
41671 is the date serial number for 2/1/2014

Or, you could use this one...

=(B1>=41640)*(B1<=41671)

[katkth7533]

Actually, neither worked. I am stumped!

[Tony Valko]

Can you post a SMALL sample file so we can see what's happening?

[katkth7533]

Hi Tony. I have attached my file. I hope this is small enough! On the tab titled "PIPELINE", in cell Z2, I want the left border to be red if the date in cell BF1 is between 9/30/14 and 10/31/14. That date (BF1) is a link to a cell on the "DATA ENTRY" tab. The thought with this file is that an end user will enter required info in the "DATA ENTRY" sheet that will populate the "PIPELINE" page which is a visual representation of the data. This sheet will be locked so no formatting will be lost. The Z2 red line is a status date line which will move each month. This formula will be in every cell in that second line above the months/years. I hope that is clear.
Thank you for looking at this. Very frustrating!

[Tony Valko]

Ok worked for me.

Since your file is in the *.xls format I did this in Excel 2002...

Select cell Z2 (is that the right cell to format?)
Goto Conditional formatting
Use the "Formula Is" option
Formula: =(BF1>=41912)*(BF1<=41943)

41912 = date serial number for 9/30/2014
41943 = date serial number for 10/31/2014

Click the Format button
Select the Border tab
Select the desired color from the drop down
Select the desired line style
In the box on the left click whichever borders you want to format
OK out