Overriding Conditional Formatting Color with Manual Entries of a different color

[dayna]

Hi Everyone! (Sorry this is so dense)

I am trying to find a way to use conditional formatting to override my existing conditional formatting, while still retaining it underneath.
I want to do this by having the conditional format recognize when I have input a date manually VS. our pre-existing formula-made dates.
I will provide screenshots below as this is hard to explain.

The colored dates below are done with multiple conditional formatting rules, and I have provided a screenshot of those as well.
Every date is a formula, based on a date off screen that we input manually, used to create a timeline / due date for each step.
Sometimes our steps are completed before or after the date estimated by the formula, and we would then input it manually.
Ideally when we input the date manually, it would automatically change the fill color of the cell, as it is no longer a formula, and you can tell it was entered by me instead.

The greying out of the entire row happens once I enter the final completion date (that today's date has passed already) in the far right column.
I would still like this conditional format rule to apply over-top of the one I'm looking for help with creating.

I've done many google searches and have tried many different ways, but none of them are giving me exactly what I am looking for.
If this can't be done with conditional formatting please let me know how else I could get it to work. (or if this is just not a thing at all!)


Thanks!!

 

[dayna]

Not home to test but can't you use

=NOT(ISFORMULA(B2))

Thanks for the reply!

When I use that formula it seems to select random text, formulas, and blanks to format? Should only really be highlighting the first column, as most of the others are formulas.
I'm not very good at excel but I didn't think this would be so hard to do haha.

 

[MARK858]

I think we need to have a look at your workbook (with any sensitive data sanitized) as all the NOT function does normally is change any result returning True to False and vice-versa any result returning False to True.
So I am not understanding why you would get

select random text, formulas, and blanks to format?

if

in a single column, as your condition "=isformula(b2)" highlights the formula cells, and what I'm trying to do is the opposite and only highlight cells that have manually inputted text/dates in them

is highlighting the exact opposite cells to what you require

 

[dayna]

I think we need to have a look at your workbook (with any sensitive data sanitized) as all the NOT function does normally is change any result returning True to False and vice-versa any result returning False to True.
So I am not understanding why you would get

if

is highlighting the exact opposite cells to what you require

Hi Mark,

Here is a link to the scrubbed workbook - Schedule - MrExcel.xlsx

Hopefully that works and is what you meant for me to post.
All of these problems could just be me being bad at excel, I keep going in circles and getting more confused while trying to figure it out.

 

[Joe4]

When I use that formula it seems to select random text, formulas, and blanks to format? Should only really be highlighting the first column, as most of the others are formulas.
I'm not very good at excel but I didn't think this would be so hard to do haha.

That usually indicates one of two things:

1. Your Conditional Formatting range and Conditional Formatting formula do not line up.
When formatting a multi-cell range, the formula you enter should be applied to/reflect the very FIRST cell in your selected range.
So, if you were so select the range C3:G100, you will write the Conditional Formatting formula as it applies to cell C3 (and Excel will automatically adjust it for all other cells).

One common mistake I see people make is they will select an entire column (i.e. column A), but they don't want the Conditional Formatting to apply to the first row. So they enter their formula with a reference to cell A2. So then the will be off one row, as row 1 will look at A2, row 2 will look at A3, etc. These two things must be in agreement with each other!

2. You have not used absolute/relative referencing correctly.
Since Excel will automatically adjust the formulas for all the other cells in your selected range, you need to lock down any range references you do NOT want to shift.
For example, let's say that you want to format the range A1:C100, but you want all the cells to look at the value in column A.
Then in your conditional formatting formula, you will need to add a "$" in front of the column A reference to lock it down, i.e. $A1
See here for more details: Excel Formulas: Relative and Absolute Cell References

 

[dayna]

That usually indicates one of two things:

1. Your Conditional Formatting range and Conditional Formatting formula do not line up.
When formatting a multi-cell range, the formula you enter should be applied to/reflect the very FIRST cell in your selected range.
So, if you were so select the range C3:G100, you will write the Conditional Formatting formula as it applies to cell C3 (and Excel will automatically adjust it for all other cells).

One common mistake I see people make is they will select an entire column (i.e. column A), but they don't want the Conditional Formatting to apply to the first row. So they enter their formula with a reference to cell A2. So then the will be off one row, as row 1 will look at A2, row 2 will look at A3, etc. These two things must be in agreement with each other!

2. You have not used absolute/relative referencing correctly.
Since Excel will automatically adjust the formulas for all the other cells in your selected range, you need to lock down any range references you do NOT want to shift.
For example, let's say that you want to format the range A1:C100, but you want all the cells to look at the value in column A.
Then in your conditional formatting formula, you will need to add a "$" in front of the column A reference to lock it down, i.e. $A1
See here for more details: Excel Formulas: Relative and Absolute Cell References

OH! Thank you so much!!

Step #1 is what got me.

I've been trying to learn what does what when formatting and creating formulas in excel, and that is exactly what I was confused by and doing wrong the whole time.


Thanks everyone for all the help!
Dayna

 

[Joe4]

Your welcome.

Glad we were able to help!