Conditional Formatting - NOT OR Statement with Text Condtions

[ExcelEngieer]

Hi,

First post here so go easy on the (possibly) stupid question.

I'm attempting to use conditional formatting to identify and highlight incorrect entries on a sheet, the entries are numeric and alphabetic, I have the numeric one working how I want it to but the alphabetic is just returning an error.

The Numeric conditioning:

=NOT(OR($G$7=" ",$G$7=1, $G$7=2, $G$7=4, $G$7=8, $G$7=12, $G$7=24, $G$7=48, $G$7=96))

Works and returns a change in colour as expected.

These return a formula error, and I'm not sure why?:

=NOT(OR($H$7=" ", $H$7="ME”, $H$7="OP”, $H$7="EL”, $H$7="CO”))

=NOT(OR($J$7=" ", $J$7=“YES”, $J$7=“NO”))

=NOT(OR($L$7=" ", $L$7=“PM”, $L$7=“PDM”, $L$7=“CM”))

And this (Intending to be used to display a previously hidden error message if there are any incorrect values entered) doesn't work either, it doesn't display an error but doesn't function to change the cell format:

=NOT(OR($G$7:$G$21=" ",$G$7:$G$21=1, $G$7:$G$21=2, $G$7:$G$21=4, $G$7:$G$21=8, $G$7:$G$21=12, $G$7:$G$21=24, $G$7:$G$21=48, $G$7:$G$21=96))

Any help would be greatly appreciated, If I haven't been clear enough pleas let me know and I will try to elaborate.

Regards,

Joe

 

[Andrew Poulsom]

Welcome to MrExcel.

You have the wrong sort of double quotes in those formulas - ” instead of ".

 

[ExcelEngieer]

Wow, thanks so much!

Something so simple and seemingly insignificant... Will teach me not to write code in notepad! - Any way around this phenomenon? At the moment I'm pasting " in place of ” in every instance... most frustrating!

Do you have any insight into the other issue as to why it isn't working?

 

[Andrew Poulsom]

Do the formulas return what you expect when entered in a cell?

 

[ExcelEngieer]

They don't, but I managed to solve in in a roundabout way - I'm sure there must be an easier way to do it in one rule but I couldn't figure it out:

Resulting in:

I have this table populating a calender, is there a way I can then put this calender into outlook?

Any help appreciated!

 

[Andrew Poulsom]

This may help you:

Blogs - The UsefulTechnology Blog - Site Home - TechNet Blogs