Conditional formating for Target and Progress

[trimiii]

Fellas,

I need assistance.... anyone can help, please.

I have a spreadsheet that I am using to calculate targets and progress (see picture below).

1. From J8 till BH, I am using the formula =IF(AND(J$4>=$F8,J$4<=$G8),"X","") to highlight in light green the timeline (from 1Sep - 20 Oct), this works fine, and because of the timeline changes to a different schedule.
2. I am seeking for formula to highlight the abovementioned into different colors when I add the overall % for the same timeline in column I8 in a different color (dark green).

That's way I can show the target and progress once % goes higher.


Thought...

 

[Fluff]

How about

+Fluff New.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1
2
3
4					01/09/2020	02/09/2020	03/09/2020	04/09/2020	05/09/2020	06/09/2020	07/09/2020	08/09/2020	09/09/2020	10/09/2020	11/09/2020	12/09/2020	13/09/2020	14/09/2020	15/09/2020	16/09/2020	17/09/2020	18/09/2020	19/09/2020	20/09/2020	21/09/2020	22/09/2020	23/09/2020	24/09/2020	25/09/2020	26/09/2020	27/09/2020	28/09/2020	29/09/2020	30/09/2020	01/10/2020	02/10/2020	03/10/2020	04/10/2020
5
6
7
8	01/09/2020	20/10/2020	49	50
9	01/09/2020	20/10/2020	49	25
10	01/09/2020	20/10/2020	49	10
11	01/09/2020	20/10/2020	49	40
Data

Cell Formulas
Range	Formula
H8:H11	H8	=DAYS(G8,F8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J8:AQ14	Expression	=J$4<=($F8+($H8*$I8/100))	text	NO
J8:AQ14	Expression	=AND(J$4>=$F8,J$4<=$G8)	text	NO

 

[trimiii]

This works just fine... thanks a lot Fluff. KUDOS

 

[Fluff]

You're welcome & thanks for the feedback.

 

[trimiii]

Hi Fluff... I am running in some issues... actually the H8 is the percentage complete, not completed day. For example if I put 50% complete it should highlight only the hall or 50%.

thoughts...

 

[Fluff]

In your image H8 is the number of days & I8 is the %

 

[trimiii]

Yeah... thats correct... however, the 2nd conditional format works from the first cell... I change the start date on F8 from 1 Sep to 15 Sep and the first conditional format works but 2nd one is still counting from the first cell... see picture... any idea or thoughts?

 

[Fluff]

Got you, how about

+Fluff New.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1
2
3
4					01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	01	02	03	04
5
6
7
8	01/09/2020	04/10/2020	33	50
9	10/09/2020	04/10/2020	24	25
10	15/09/2020	04/10/2020	19	10
11	20/09/2020	04/10/2020	14	15
Data

Cell Formulas
Range	Formula
H8:H11	H8	=DAYS(G8,F8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J8:AQ14	Expression	=AND(J$4>=$F8,J$4<=$F8+($H8*$I8/100)-1)	text	NO
J8:AQ14	Expression	=AND(J$4>=$F8,J$4<=$G8)	text	NO

 

[trimiii]

Hi Mate,

now it works... thanks again and highly appreciate your input and support.

KUDOS to you... thanks again

 

[Fluff]

Glad to help & thanks for the feedback.