conditional formatting based on weekly target

[Sanjibghosh]

I want to make conditional formatting based on weekly target. can any one help me on it

[Hercules1946]

Hello
1. Firstly, select cells G5:CA9 in the worksheet.
2. On the Home Ribbon, click Conditional Formatting>New Rule
3. Click 'Use a formula to determine which cells to format'.
4. In the formula box, type =G5>= G$1
5. Click Format>Fill
6. Click the Green Icon >OK>OK.
7. Repeat steps 2-6 typing the formula = G5<G$1, and selecting the Red Icon.
8. Finished

[avk]

Look attach file.
Firstly clear all "CF" in entier sheet.
Select "G5:G24
>Go to CF > Manage Rules > New Rule
>Select "Use a formula to determine which cells to format"
>enter formula

Please Login or Register  to view this content.

>Go to Format & select Green > click "ok"
>"click again "ok"
>Clcik "apply" & "ok"
Same process for Less than only formula change to

Please Login or Register  to view this content.

For Red color
In another column same process.

[Hercules1946]

@avk
Why not select the whole block instead of just G5:G24?
Also remove = from second formula

[Sanjibghosh]

Thanks you so much.

I need one more help on conditional formatting. i want to highlight the cells by conditional formatting based on Start date and end date. please help me?

[FDibbins]

Try this for the rule...
=AND(O$7>=$G8,O$7<=$H8)

[avk]

Dear Hercules : Range G5:G24 : Just for one example. Other column CF by own.

Dear Sanjibghosh : You mentioned Start date and end date. But this information not completed.
Kindly confirm In which columns you need CF. (where to apply CF)
What is criteria (against which Target)

[Hercules1946]

Dear Hercules : Range G5:G24 : Just for one example.

I understand that, but my point was that if you select the whole block (from G to CA) that is one conditional format to define instead of a separate format for each column

[Sanjibghosh]

Thanks you so much.

Now, I want to add one more conditional formatting. If "Man Days Req (Based on current trand)" date is more than "End Date" then the date cells are red.
I have marked manually in the excel file for example see cell "BG9:BW9". please help me?

[JohnTopley]

None of the cells in BG onward are coloured RED:

Are you saying that for example in row 9, dates from 1st June to 23rd June are RED i.e dates after "End Date" up to "Man Days Req (Based on current trend)" ?

If the above

CF formula

Select P8

=AND(P$7>$H8,$I8>$H8,P$7<=$I8)

Applies to:

=$P$8:$CB$100

[Hercules1946]

None of the cells in BG onward are coloured RED:

@John
I think the OP meant PINK (Grill Pattern)

[JohnTopley]

... if my logic is correct, then we can easily change the "fill".

[Sanjibghosh]

Hi,

i want to calculate how many resources are allocated on today basis based on project name. The no. of resource calculate from B column in "Resource Allocation" sheet and the count shows in "Account Status" sheet under column L

[AliGW]

Entered as an array formula (CTRL+SHIFT+ENTER):

=SUM(IF('Resource Allocation'!$C$2:$CB$2=TODAY(),IF('Resource Allocation'!$C$3:$CB$30='Account Status'!$B8,'Resource Allocation'!$B$3:$B$23)))

[Sanjibghosh]

Thank you so much

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Any further queries not directly related to conditional formatting should be made in a new thread. Thanks!

[Sanjibghosh]

If "Man Days Req (Based on current trand)" date is less than "End Date" then the date cells are Green .

For example Row 8 : "U8:AX8" cells are green

Please let me know the formula for conditional formatting.

[JohnTopley]

You should be able to work this out yourself given the number of previous examples given to you

try

=$R8<$H8

[Sanjibghosh]

Hi JohnTopley,

I have done it as you said but its not working. Can you do it in the file? It will help me a lot

[JohnTopley]

See the attached.

You need to check the "Applies to" is set to the required range.

[Sanjibghosh]

Hi,

Now, i am facing very difficult challenges. I want to update the project name in "Resource Allocation" sheet based on "Ideal Campaign Complete Date" from "Account Status" sheet. There is any way to update automatically.

For example i have updated manually in "Resource Allocation" for project name "Red Hat_TQL"

[AliGW]

This has now moved a long way away from the original purpose of the thread and has nothing to do with its title. I asked you in post #16 to start a new thread on any subsequent new issue - you have ignored this request.

Please start a new thread with an appropriate title.

Thread closed.