New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Conditional Formatting Rule

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Conditional Formatting Rule

Postby rtommie84 » Fri Sep 09, 2016 3:01 pm

I have some experience using Excel's basic functions and thought it would be simple to create a conditional formatting rule, but I've discovered that it's a little more complicated than I anticipated. The rule would:

(1) check the date in B1; if the date is greater than or equal to today (TRUE)
(2) check to see if B2 is blank, if TRUE apply the conditional formatting cells B1:B3.

This Conditional Formatting rule would apply to $B$1:$E$9.

Note: The values in rows 2, 5 and 8 are limited to a 1 or -1.

In my screen shot example that's attached, the rule would apply to cells B1:B3 and D4:D6 because the dates in B1 and D4 are greater than today and B2 and D5 are empty.

I've tried =AND(TODAY()>=$B1,B2=""), but I'm not getting my desired results

I would appreciate your help letting me know what I'm doing wrong.

CF.jpg
  • 0

You do not have the required permissions to view the files attached to this post.
rtommie84
Rookie
 
Posts: 2
Joined: Sep 9, 2016
Reputation: 0
Excel Version: 2016

Re: Conditional Formatting Rule

Postby pecoflyer » Sat Sep 10, 2016 2:21 am

Hi
the first thing you should do is get rid of merged cells, they are nothing but trouble
Then try applying your CF again
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Conditional Formatting Rule

Postby rtommie84 » Sat Sep 10, 2016 6:32 am

pecoflyer,

Thanks for your comments; however, that did not work. The only merged cells are in column A, after removing the merged cells and reapplying the CF, I have the same results.
  • 0

rtommie84
Rookie
 
Posts: 2
Joined: Sep 9, 2016
Reputation: 0
Excel Version: 2016

Re: Conditional Formatting Rule

Postby pecoflyer » Sun Sep 11, 2016 3:05 am

Please post a sample sheet so we can work on it
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Conditional Formatting Rule

Postby ConneXionLost » Mon Sep 12, 2016 4:20 pm

Due to the relative arrangement of the cell groups your calendar is employing, you will need to apply the conditional format individually three times to get your desired results. If you don't, Excel will autoshift the relative references and basically give you the results you already got. (Note - In your original results, rows 1, 4, and 7 are actually working, although cell D4 is only coincidentally correct.)

Focus on just one cell in your first day to build the format. In cell B1, I suggest your original formula less the absolute reference:

Code: Select all
=AND(TODAY()>=B1,B2="")


Then move to cell B2, and build the format for that cell. Same formula, but now that your entering the format from the B2 point-of-view, Excel will consider it a different CF.

Finally, move to B3 and build that format.

Once you have completed these original three, you'll be able to highlight all three cells and use the format painter tool to apply the formats to the rest of your calendar.

Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 38 guests