Conditional Formatting with a twist

Hi there,

I have a roster where i want to implement conditional formatting.

If you see my roster below, you will see the green and red fields.* When any number up to 8 is entered in the "Normal Hours" column, I want it to be GREEN and if above that, i want it to be ORANGE. And for the Overtime hours column i want it to automatically go RED when any number is entered.*
For the TOTAL HOURS box, i want to use the Icon set and utilise the tick and apostrophe icons. When the total hours are '213' it's always a green tick but if it's less than 213 or more than 213 i want it to be a yellow apostrophe.

Can someone assist with this setup please?


roster.PNG

FlameRetired

Re: Conditional Formatting with a twist
Hello DIKIC07. Welcome to the forum.

Pics and screen shots don't help much ... if at all.

Uploading a representative Excel file gives context and real data to work with.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

click “Go Advanced” (next to Post Quick Reply – bottom right),
scroll down until you see “Manage Attachments”, click that,
click “Browse”.
select your file(s)
click “Upload”
click “Close window”
click “Submit Reply”

The file name will appear at the bottom of your reply.

Hi FlameRetired,

Thank you for a swift response, it's much appreciated.

I have included the excel template without sensative information to response.

Apologies for that. I will go and read the rules so i understand the requirements. Thanks for the heads up.

Yes, i made a mistake, its supposed to say row rather than column. And that's a good pickup. I need to remove 'Hours Worked' and replace it with 'Normal Hours'.

Normal hours amount to 8 with GREEN background, anything more than 8 should make the background go to ORANGE to identify a problem.

When overtime hours are added, the Overtime box background needs to go into RED.

No problems about further clarifications - ask away .... I appreciate your help with this.

There is no need for these amendments in the AD-HOC security section.
That section has no regulations.

FlameRetired, thank you very much for that. That will help me minimise mistakes when checking over the hours.

In regards to the tick and apostrophe i was talking about, if you look under Conditional Formatting > Icon Sets ... its there ... cells K55/56 and K110/111 have the total for the week. I wanted a green tick to appear if the total is 213 and an orange apostrophe if its anything under or over 213 ...

FlameRetired, thank you very much for that. That will help me minimise mistakes when checking over the hours.

In regards to the tick and apostrophe (my mistake, was meant to say exclamation mark) i was talking about, if you look under Conditional Formatting > Icon Sets ... its there ... cells K55/56 and K110/111 have the total for the week. I wanted a green tick to appear if the total is 213 and an orange exclamation mark if its anything under or over 213 ... kvsrinivasamurthy has done it but for the wrong total hours. I don't need it per person, just the total hours for the week, for everyone - cells K55/56 and K110/111.

Interesting how you did that kvsrinivasamurthy - i could hsve sworn i tried that and it wasn't working. I must have been doing something wrong. And thank you for your contribution kvsrinivasamurthy.

Originally Posted by kvsrinivasamurthy

CF done for all cells of TOTAL HOURS WORKED. Values in rows 9, 15, 21 are changed to show working in TOTAL HOURS WORKED cells.
> 213---> Yellow apostrophe
=213----> Green Tick
< 213---> Yellow apostrophe

kvsrinivasamurthy i applied your method into cells K55/56 and K110/111 and it works perfectly. Great job. Thank you.