Conditional Formatting

T

Taryn A

New Member
Joined
Jun 29, 2013
Messages
38
I have an Excel workbook with a lot of dates that are colored coded as follows

Red indicates that the date in the cell is 13 months past the date entered in the cell

Orange indicates that the date in the cell is 1 year past the date in the cell

Green indicates its within the year of the date entered in the cell.

As the workbook is set up now i have to manually change the color of the cell.

i know there is a formula using conditional formatting that can automatically change the date but I'm not sure what that formula is. I will need this for most of the workbook. Can anyone help?
 
Sort by date Sort by votes
Haven't had a chance to do it, but I will try and see if it will work tomorrow. Thanks again!
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
etaf said:
Thats OK - does it work for you now ?
Click to expand...

Okay, I've just now had some time to try and attempt this. it is not working though...not sure why because I copied and pasted your formula so it can't be that.

Let me ask you this when I click on Conditional formatting on the tool bar I am to choose New Rule correct? Then from there what am I to choose "format all cells based on their values, format only cells that contain or use a formula to determine which cells to format?" I was selecting the last one use a formula to determine which cells to format. Then I copied and pasted the formula for the first rule and formatted so that it would be red and clicked the "stop if true" box, but nothing happened? UGH so frustrated lol!
 
Upvote 0
for 2007 or 2010 excel version


Conditional Formatting


Highlight applicable range >>


select the range you want to apply



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



now add the formula

Format… [Number, Font, Border, Fill]
OK >> OK

that should work - perhaps a link to the actual dummy sheet may help

sorry, but I'm no longer around during the week, so can only reply at weekends
 
Upvote 0
Good afternoon, I'm sorry it has taken so long for me to respond. I think I have it, but the only thing is I need for the columns next to the columns that contain the dates to be the same colour, but all that is in the columns next to it is a number 1. So I'm not sure that it is possible. Additionally, I have the names that also should match. Is there some way that I can email you one of the sheets so that you can understand what I'm saying?



Conditional Formatting


Highlight applicable range >>


select the range you want to apply



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



now add the formula

Format… [Number, Font, Border, Fill]
OK >> OK

that should work - perhaps a link to the actual dummy sheet may help

sorry, but I'm no longer around during the week, so can only reply at weekends[/QUOTE]
 
Upvote 0
if you send a PM , i can send you my email, we dont put emails onto forums
or you can use a share site, dropbox and post a link here
 
Upvote 0
etaf said:
if you send a PM , i can send you my email, we dont put emails onto forums
or you can use a share site, dropbox and post a link here
Click to expand...

Hey there, sorry for taking so long to get back on here! I try to do this when I have time. Forgive me for my ignorance, but what is a PM? I have applied your fomrating, and it works; however, there are other columns that I need formulas for (I guess). I've copied and pasted part of the spreadsheet below. I've figured out some of the formulas, but for instance if one of the dates is coded orange I need the "Cleared" column to reflect orange and the "Last", "First" & "MI" columns. Same goes if one of the dates is red or green. If there are blank cells in any of the columns they should be red which means the name, and cleared columns will also be red. So if the "Cleared" column has a 1 under "No" it would be colored red so I would want the name to be colored red. Only the date and wherever the "1" is placed should be colored. As you can see below there is 4 columns under the heading "Medical Evaluations" so the only column that should be colored is where the "1" is placed. I also have a "Limited" column under each heading. If a "1" is placed under the "Limited" column the date and the cell with the 1 should be colored yellow. Thanks in advance for your help!

Coles District</SPAN>
Medical Evaluations</SPAN>
OSHA Questionnaire Restrictions</SPAN>
Fit Test </SPAN>
Cleared</SPAN>
Last</SPAN>
First</SPAN>
MI</SPAN>
Med Eval Date</SPAN>
Full</SPAN>
Limited</SPAN>
Failed</SPAN>
OSHA Date</SPAN>
None</SPAN>
Limited</SPAN>
Failed</SPAN>
Date</SPAN>
Pass</SPAN>
Fail</SPAN>
Yes</SPAN>
No</SPAN>
</SPAN>
Taylor</SPAN>
</SPAN>
12/11/2013
1</SPAN>
12/11/2013
1</SPAN>
1/4/2014
1</SPAN>
1
</SPAN>
Dewayne
</SPAN>
Blank (should be red)
Red
Blank (should be red)
Red
Blank (should be red)
Red
1
Ryan</SPAN>
4/16/2013
1</SPAN>
4/16/2013
1</SPAN>
1/10/2013
1</SPAN>
1
</SPAN>
Daniel</SPAN>
</SPAN>
8/29/2013
1</SPAN>
8/29/2013
1</SPAN>
10/21/2013
1</SPAN>
1
</SPAN>
Joseph</SPAN>
1/28/2013
1</SPAN>
1/28/2013
1</SPAN>
1/10/2013
1</SPAN>
1
Jonathan</SPAN>
4/16/2013
1</SPAN>
4/16/2013
1</SPAN>
10/21/2013
1</SPAN>
1
</SPAN>
Stanton</SPAN>
4/16/2013
1</SPAN>
4/16/2013
1</SPAN>
10/21/2013
1</SPAN>
1
Christopher</SPAN>
4/16/2013
1</SPAN>
4/16/2013
1</SPAN>
10/21/2013
1</SPAN>
1
</SPAN>
Justin</SPAN>
8/29/2013
1</SPAN>
8/29/2013
1</SPAN>
6/7/2013
1</SPAN>
1

<TBODY>
</TBODY>
 
Upvote 0
etaf said:
sorry, i really dont follow the paragraph

i dont know what a "cleared" column means

read the paragraph a few times

perhaps you could write the rules out separately on different lines

PM = private messages
http://www.mrexcel.com/forum/private.php
where you can send messages to other users on the form
http://www.mrexcel.com/forum/private.php?do=newpm
Click to expand...

Cleared means that they are fully cleared to participate, does that make more sense? The problem is there are so many variables. Did you by chance read the entire thread? Were you able to scroll over and see the "Cleared" column? Let me try and make sense lol!

Okay so if under the "Cleared" column or any of the columns that have dates are filled red I would need for the Cleared column to be filled red with a number 1 under "No". If any of the dates are filled orange than I would need the Cleared column to be filled orange with a 1 under "Yes". If all the dates are filled green than I would need the cleared column to be filled green with a number 1 under yes. If any of the columns with dates are left blank than they would be filled red and the cleared column would also be filled red with a number 1 under "No".

The conditional formatting based on today's date

cell A6 = 3/26/10 - RED - 15 months
cell A7 = 4/24/12 - orange - 12 months
cell A8 = 4/17/13 - green - less than 12 months

Does this make more sense? Is there a way that I can actually post a piece of the spreadsheet as I did below but actually have it as a chart?</SPAN>
 
Upvote 0
See if this helps any

Color Code Key<o:p></o:p>
<o:p></o:p>
Full Participation<o:p></o:p>
<o:p></o:p>
Limited Participation<o:p></o:p>
<o:p></o:p>
Failed/Missing/Expired (Indicates Medical/OSHA or Fit Test Date has exceeded 15 months)<o:p></o:p>
<o:p></o:p>
Out of Date (Indicates Medical/OSHA or Fit Test Date has exceeded 12 months)<o:p></o:p>
<o:p></o:p>
Inactive Status (College, Extended Injury of Sick Leave, Deployment, Personal Reasons)<o:p></o:p>

<TBODY>
</TBODY>



District<o:p></o:p>​
<o:p></o:p>

Med Evals<o:p></o:p>​
<o:p></o:p>

OSHA Questionnaire Restrictions<o:p></o:p>​
<o:p></o:p>

Fit Test<o:p></o:p>​

Cleared<o:p></o:p>​

Last<o:p></o:p>​

First<o:p></o:p>​

M<o:p></o:p>​

<o:p></o:p>​

Med Eval Date<o:p></o:p>​

Full<o:p></o:p>​

Limited<o:p></o:p>​

Failed<o:p></o:p>​

<o:p></o:p>​

OSHA Date<o:p></o:p>​

None<o:p></o:p>​

Limited<o:p></o:p>​

Failed<o:p></o:p>​

<o:p></o:p>​

Date<o:p></o:p>​

Pass<o:p></o:p>​

Fail<o:p></o:p>​

Yes<o:p></o:p>​

No<o:p></o:p>​

Doe<o:p></o:p>​

John<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1/29/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1/29/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

3/5/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

Smith<o:p></o:p>​

Jane<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

8/29/2013<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

8/29/2013<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

10/21/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

Jones<o:p></o:p>​

Mike<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

4/16/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

4/16/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

4/18/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

Wood<o:p></o:p>​

Mark<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

Simpson<o:p></o:p>​

Joe<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

9/14/2012<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

9/14/2012<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

10/15/2012<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

Morris<o:p></o:p>​

Lu<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

4/16/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

4/16/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

Anderson<o:p></o:p>​

Luke<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1/29/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1/29/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

11/20/2012<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

Burns<o:p></o:p>​

Sara<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

10/1/2013<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

10/1/2013<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

Cruz<o:p></o:p>​

Jose<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

2/4/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

2/4/2013<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

2/4/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

Ellis<o:p></o:p>​

Jason<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

3/5/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

3/5/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

<o:p></o:p>​

3/2/2014<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

1<o:p></o:p>​

<o:p></o:p>​

<TBODY>
</TBODY>
 
Last edited:
Upvote 0
Okay, everytime I send you the chart it doesn't want to keep it's color so I'm trying this way. Hopefully it will make more sense to you.



MedPhysicalProgramSampleDoc_zpse1f99399.jpeg
[/URL][/IMG]
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Colour cell/conditional formatting
Replies
2
Views
127
Webslinger83
W
J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
289
etaf
etaf
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
287
Insurance Dave
I
E
  • Question
Interest Due Log
Replies
7
Views
193
ExcelNew7
E
Y
  • Question
Conditional formatting
Replies
3
Views
145
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,491
Messages
6,125,107
Members
449,205
Latest member
ralemanygarcia

Share this page

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