Format entire row ??

format one cell correctly then use format painter along the lines of

=if(isdate(A1)=true format as black

second condition =if(isdate(A1)=false format as red

If you want to format the entire row based on the contents in a single, static cell, you will probably need to use absolute cell referencing and lock the column in your Conditional Formatting formula down, i.e. use $A1 instead of A1 (if A was the column you were using).

oldbrewer said:

format one cell correctly then use format painter along the lines of

=if(isdate(A1)=true format as black

second condition =if(isdate(A1)=false format as red

Click to expand...

sorry bit of a noob,

I was researching this a month or so back, and was able to find a way of conditionally formatting with only one condition. I cant remember where, but I was able to select my entire spreadsheet as what I wanted to apply the condition to and then with the parameters set, was able to have the single condition with one formula.

what is the reference for excel to check a cell for "date" instead of say a "word" or "number".

If you highlight the whole range you want to apply the Conditional Formatting to, and then write the Conditional Formatting formula as it applies to just the first row in your range, Excel is smart enough to adjust the formula for all the other rows, as long as you have made proper use of the Absolute Range References, like I mentioned in my previous post (where you want to lock down the column, but not the row).

Note, there is no ISDATE function in Excel. In Excel, dates are actually stored as numbers, specifically the number of days since 1/1/1900. Dates are just numbers with date formats. So you can use the ISNUMBER function. If you want/need to differentiate dates and numbers, that gets a little trickier, as Excel formula just look at the underlying values in the cells and not their formats. You might need to use VBA if you need to differentiate the two.

Or, you may be able to enhance your Conditional Formatting formula to only look for dates in some specified date range.

you are entering dates close to today's date so how about checking if the date is within +/- 3 months of today and if so turning the font black - all other cells can have a default font of red which will be forced black when the condition is met ?

oldbrewer said:

you are entering dates close to today's date so how about checking if the date is within +/- 3 months of today and if so turning the font black - all other cells can have a default font of red which will be forced black when the condition is met ?

Click to expand...

yes default is red, date will range widely could be months before said task is completed, once completed a date is entered, then entire row needs to change to black font

So is there still a question/issue?

oldbrewer and I have given you lots of information on how to do this. If you still need help, please explain exactly which part you are having trouble with. It may also be helpful if you could present an actual example with cell addresses and values, so we can craft a formula that meets your layout exactly.

yeah so Im not sure how to apply the info you guys presented.....

however, I found that using this formula

highlight whole spread sheet

set format conditions for font to turn black

then apply this formula

=$G1="?"

and It yields the desired result of the entire row changing to black as long as there is a "?" in the cell column G. my issue is getting excel to look for a date or as you said a number with out it being specific. I just want excel to look in column G and if there is a date present change that row to black font.

as I stated, the above formula works perfect, so how do I reference the "date"

As I said, in Excel dates really are numbers, so you are going to have to put some thought in to this.

Will you have some numeric non-date numbers in these columns you will be looking at (i.e. column G)?
If so, what do they look like?
What is the absolute smallest and largest numbers that they may be?

Likewise, for your dates in this column, can you tell us what the absolute smallest/largest values of these dates may be?

If there is not overlap between the possible number and date values, we should be able to do this. But if you want us to help you with that, you are going to have to provide those details I am asking for.