Conditional formatting

[MCB1973]

Hi All,

i need some help please, what i need to do is when a number is entered into Col G, i need Col A to change color, BUT the issue I have is the data is floating in COL C-F, so this changed depending on the event registered, this there any way to change the color in COL C if it moves a line with out changing the fixed data in COL G, any help would be appreciated.



C D E F G

 

[etaf]

ok, so back to your orginal post

what i need to do is when a number is entered into Col G, i need Col A to change color, BUT the issue I have is the data is floating in COL C-F, so this changed depending on the event registered, this there any way to change the color in COL C if it moves a line with out changing the fixed data in COL G, any help would be appreciated.

your sample has 4 sheets
overspeed - A&B are black
driver data - G has test/aaa otherwise ALL zeros - so - not sure what examples you have - showing the input in g and expected colour of a


what sheet do you want the conditional formatting added to

still confused , even with a test sheet
but still need examples and a full explanation

i can put the sheet on the forum with XL2BB - once i know what sheet you are using

 

[MCB1973]

ok, so back to your orginal post


your sample has 4 sheets
overspeed - A&B are black
driver data - G has test/aaa otherwise ALL zeros - so - not sure what examples you have - showing the input in g and expected colour of a


what sheet do you want the conditional formatting added to

still confused , even with a test sheet
but still need examples and a full explanation

i can put the sheet on the forum with XL2BB - once i know what sheet you are using

ok so the main sheet is what i need to able to match Col G with Col A and change COL A color, but as the data changes say for instance , 1818 and 2022D changed positions the issue is getting the formatting to follow COL A with COL G Data being fixed data.... hope that makes sense?

The highest number should be on on the top in COL F as well, unfortunately some of the data got stuffed up when it was placed in DOC's, i have corrected this error

 

[etaf]

the main sheet ?
that the sheet named - overspeeds ??? otherwise i dont know which you class as main sheet
using the image you have shown

Col G with Col A and change COL A color,

column A has nothing in to match , and is just like a black border

1818 and 2022D changed positions the issue is getting the formatting to follow COL A with COL G Data being fixed data.... hope that makes sense?

Column C has those values in , so not sure about column A
Column G is just yellow

really trying to help, but i'm just not following , remember i know very little about your spreadsheet setup - so need to refer to sheet names (not main sheet) and columns in that sheet name
added as a XL2BB here

Overspeed Report - Test Sheet (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Driver Overspeed Report
2
3			# Events 105km/h & Less than 110km/h							Events 110km/h & Over
4
5			Fleet	Driver	Depot	105 & over	Enter Truck No. Sentinel Message Sent to:	Enter Truck No. of Truck Called After 20 Events:		Fleet	Driver Events over 200km/h will not show on this report	Depot	110 & over	Enter Truck No. of Truck Called After 2 Events:
6				(D/L:)						0	(D/L:)	0	0
7			2202D	AAA (D/L:123DEF)	AAA	217				0	(D/L:)	0	0
8			1818	TEST (D/L:ABC123)	TEST	35				0	(D/L:)	0	0
9			0	(D/L:)	0	0				0	(D/L:)	0	0
10			0	(D/L:)	0	0				0	(D/L:)	0	0
11			0	(D/L:)	0	0				0	(D/L:)	0	0
12			0	(D/L:)	0	0				0	(D/L:)	0	0
13			0	(D/L:)	0	0				0	(D/L:)	0	0
14			0	(D/L:)	0	0				0	(D/L:)	0	0
15			0	(D/L:)	0	0				0	(D/L:)	0	0
16			0	(D/L:)	0	0				0	(D/L:)	0	0
17			0	(D/L:)	0	0				0	(D/L:)	0	0
18			0	(D/L:)	0	0				0	(D/L:)	0	0
19			0	(D/L:)	0	0				0	(D/L:)	0	0
20			0	(D/L:)	0	0				0	(D/L:)	0	0
Overspeeds

Cell Formulas
Range	Formula
C6:C20	C6	=Data2!H2
D6:D20	D6	=Data2!I2&" "&"(D/L:"&Data2!J2&")"
E6:E20	E6	=Data2!K2
F6:F20	F6	=Data2!G2
J6:J20	J6	=Data2!T3
K6:K20	K6	=Data2!U3&" "&"(D/L:"&Data2!V3&")"
L6:L20	L6	=Data2!W3
M6:M20	M6	=Data2!S3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G6:G166	Expression	=F6>=5	text	NO
F6:F166	Cell Value	>=20	text	NO
F6:F166	Cell Value	>=5	text	NO
N6:N132	Expression	=M6>=5	text	NO
M6:M132	Cell Value	>=5	text	NO
H6:H166	Expression	=F6>=20	text	NO
N6:N132	Expression	=M6>=2	text	NO
G6:G166	Cell	does not contain a blank value	text	NO
H6:H166	Cell	does not contain a blank value	text	NO
C6:F166	Cell Value	=0	text	NO
J6:J132	Expression	=M6=0	text	NO
K6:K132	Expression	=M6=0	text	NO
L6:L132	Expression	=M6=0	text	NO
J6:M132	Cell Value	=0	text	NO
C9	Expression	=$F$9>=20	text	NO
C9	Expression	=$F$9>=5	text	NO
C6:C30	Expression	=$F$6>=20	text	NO
C7:C30	Expression	=$F$7>=20	text	NO
C8	Expression	=$F$8>=20	text	NO
C8	Expression	=$F$8>=5	text	NO
C10	Expression	=$F$10>=20	text	NO
C10	Expression	=$F$10>=5	text	NO
C11	Expression	=$F$11>=20	text	NO
C11	Expression	=$F$11>=5	text	NO
C12	Expression	=$F$12>=20	text	NO
C12	Expression	=$F$12>=5	text	NO
C13	Expression	=$F$13>=5	text	NO
C14	Expression	=$F$14>=5	text	NO
C15	Expression	=$F$15>=5	text	NO
C16	Expression	=$F$16>=5	text	NO
C17	Expression	=$F$17>=5	text	NO
C18	Expression	=$F$18>=5	text	NO
C19	Expression	=$F$19>=5	text	NO
C20	Expression	=$F$20>=5	text	NO
C6:C132	Expression	=F6<=9	text	NO
C6:C29	Expression	=$F$6>=5	text	NO
C7	Expression	=$F$7>=5	text	NO
C8	Expression	=$F$8>=5	text	NO
C9	Expression	=$F$9>=5	text	NO
C6:C166	Expression	=AND(G6 <> "" )	text	NO

 

[MCB1973]

the main sheet ?
that the sheet named - overspeeds ??? otherwise i dont know which you class as main sheet
using the image you have shown


column A has nothing in to match , and is just like a black border

Column C has those values in , so not sure about column A
Column G is just yellow

really trying to help, but i'm just not following , remember i know very little about your spreadsheet setup - so need to refer to sheet names (not main sheet) and columns in that sheet name
added as a XL2BB here

Overspeed Report - Test Sheet (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Driver Overspeed Report
2
3			# Events 105km/h & Less than 110km/h							Events 110km/h & Over
4
5			Fleet	Driver	Depot	105 & over	Enter Truck No. Sentinel Message Sent to:	Enter Truck No. of Truck Called After 20 Events:		Fleet	Driver Events over 200km/h will not show on this report	Depot	110 & over	Enter Truck No. of Truck Called After 2 Events:
6				(D/L:)						0	(D/L:)	0	0
7			2202D	AAA (D/L:123DEF)	AAA	217				0	(D/L:)	0	0
8			1818	TEST (D/L:ABC123)	TEST	35				0	(D/L:)	0	0
9			0	(D/L:)	0	0				0	(D/L:)	0	0
10			0	(D/L:)	0	0				0	(D/L:)	0	0
11			0	(D/L:)	0	0				0	(D/L:)	0	0
12			0	(D/L:)	0	0				0	(D/L:)	0	0
13			0	(D/L:)	0	0				0	(D/L:)	0	0
14			0	(D/L:)	0	0				0	(D/L:)	0	0
15			0	(D/L:)	0	0				0	(D/L:)	0	0
16			0	(D/L:)	0	0				0	(D/L:)	0	0
17			0	(D/L:)	0	0				0	(D/L:)	0	0
18			0	(D/L:)	0	0				0	(D/L:)	0	0
19			0	(D/L:)	0	0				0	(D/L:)	0	0
20			0	(D/L:)	0	0				0	(D/L:)	0	0
Overspeeds

Cell Formulas
Range	Formula
C6:C20	C6	=Data2!H2
D6:D20	D6	=Data2!I2&" "&"(D/L:"&Data2!J2&")"
E6:E20	E6	=Data2!K2
F6:F20	F6	=Data2!G2
J6:J20	J6	=Data2!T3
K6:K20	K6	=Data2!U3&" "&"(D/L:"&Data2!V3&")"
L6:L20	L6	=Data2!W3
M6:M20	M6	=Data2!S3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G6:G166	Expression	=F6>=5	text	NO
F6:F166	Cell Value	>=20	text	NO
F6:F166	Cell Value	>=5	text	NO
N6:N132	Expression	=M6>=5	text	NO
M6:M132	Cell Value	>=5	text	NO
H6:H166	Expression	=F6>=20	text	NO
N6:N132	Expression	=M6>=2	text	NO
G6:G166	Cell	does not contain a blank value	text	NO
H6:H166	Cell	does not contain a blank value	text	NO
C6:F166	Cell Value	=0	text	NO
J6:J132	Expression	=M6=0	text	NO
K6:K132	Expression	=M6=0	text	NO
L6:L132	Expression	=M6=0	text	NO
J6:M132	Cell Value	=0	text	NO
C9	Expression	=$F$9>=20	text	NO
C9	Expression	=$F$9>=5	text	NO
C6:C30	Expression	=$F$6>=20	text	NO
C7:C30	Expression	=$F$7>=20	text	NO
C8	Expression	=$F$8>=20	text	NO
C8	Expression	=$F$8>=5	text	NO
C10	Expression	=$F$10>=20	text	NO
C10	Expression	=$F$10>=5	text	NO
C11	Expression	=$F$11>=20	text	NO
C11	Expression	=$F$11>=5	text	NO
C12	Expression	=$F$12>=20	text	NO
C12	Expression	=$F$12>=5	text	NO
C13	Expression	=$F$13>=5	text	NO
C14	Expression	=$F$14>=5	text	NO
C15	Expression	=$F$15>=5	text	NO
C16	Expression	=$F$16>=5	text	NO
C17	Expression	=$F$17>=5	text	NO
C18	Expression	=$F$18>=5	text	NO
C19	Expression	=$F$19>=5	text	NO
C20	Expression	=$F$20>=5	text	NO
C6:C132	Expression	=F6<=9	text	NO
C6:C29	Expression	=$F$6>=5	text	NO
C7	Expression	=$F$7>=5	text	NO
C8	Expression	=$F$8>=5	text	NO
C9	Expression	=$F$9>=5	text	NO
C6:C166	Expression	=AND(G6 <> "" )	text	NO

Sorry my bad, brain fart wasn’t concidering the backed out col…. Should have said col c and col g my apologies, was trying to do this and answer phone and Do a report at the same time….

 

[etaf]

Sorry my bad, brain fart wasn’t concidering the backed out col…. Should have said col c and col g my apologies, was trying to do this and answer phone and Do a report at the same time….

ok, i understand you may have a lot of things on.

 

[MCB1973]

ok, i understand you may have a lot of things on.

Yeh I did, day off today thankfully….