Forum Discussion
Conditional Formatting - How do I get an entire row to change color based on any date being entered.
Main question is what else except dates could be in such cells. In Excel dates are actually numbers.
SergeiBaklan uhm, perhaps letters, words, numerals that are not date formatted; you can even paste photos in a cell if you like. Hope that helps Mr. MVP.
Formulas in Excel, includes one used in conditional formatting, works with cell values, not with properties like formats.
Other words, if we have 25 in A1 and Jan 25,1900 in B1, =A1=B1 returns TRUE since both cells have exactly the same value, even if formatted differently.
If no other logic to separate numbers which are not considered as dates, afraid that's with VBA programming.
Perhaps if I tell you what I am trying to do it may make more sense. I use a scheduling application that doesn't carry over formatting when pasting into Excel. I did create a template that addresses column widths, cell height, freezing the first row, filtering, etc. But because the difference in rows between each heading varies for each weekly schedule I post into Excel, I cannot predict which rows will be a heading, therefore cannot "pre" format the rows.
Below is an example of a weekly schedule after pasting it into my template in Excel. What I would like Excel to do is:
1) If any date is present in the "A" column, make that row a certain color (the rows in this example are colored because I did it manually; when pasting from the scheduling tool, all rows are white.).
2) If any date is present in the "A" column, "clear contents" in columns J, K, L, and M. (in my screen shot those columns do not have any contents because I had already manually deleted them).
As variant you may apply the rule
to entire range. Or, if cells in column B for the rows with dates are always empty, we may use rule formula =ISNUMBER($A1)*($B1="") for more reliability.
Conditional formatting can't delete any content, it only may hide it. You may apply another rule with the same formula to columns J:M, but in formatting use for the font same color as for the background