Conditional Formatting (Color)

[pandwra14]

How can I change the row section´s color based on other cells´ colors?

More precisely: IF (D6=green AND D7=green AND D8=green), D1 to D5 green

 

[kevin9999]

To use your example, are cells D6:D8 coloured manually, or as a result of conditional formatting?

 

[Drrellik]

=IF(AND(D6= ,D7= ,D8= ), ,"") would be the syntax for the if(and statement but I think you will need VBA to check for the internal cell color

 

[pandwra14]

To use your example, are cells D6:D8 coloured manually, or as a result of conditional formatting?

Manually

 

[pandwra14]

=IF(AND(D6= ,D7= ,D8= ), ,"") would be the syntax for the if(and statement but I think you will need VBA to check for the internal cell color

Would you know how to do wit with VBA?

 

[kevin9999]

Please post a copy of your sheet using the XL2BB - Excel Range to BBCode and indicate how you want the vba to be triggered. Note that there's nothing in Excel that can detect a change in cell colour that was done manually, vba is the way to go.

 

[pandwra14]

Please post a copy of your sheet using the XL2BB - Excel Range to BBCode and indicate how you want the vba to be triggered. Note that there's nothing in Excel that can detect a change in cell colour that was done manually, vba is the way to go.

Mitarbeiter Stand 0189cc7efaecf475826e0e4e232d7587a0925914e1d34a9a9e3ae73eed71948679.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Name	Hours	Sex	Contract	Business email	Private email	Phone number	Comments	Training 1	Training 2	Training 3	Training 4	Training 5	Training 6	Training 7	Training 8
3	John Smith	80+	m	yes					x		x	x			x	x
4	Rebecca Anderson	80+	f	yes					x	x					x
5	Michael Brown	MJ	m	yes					x	x	x	x		x
6	Alex Martin	80+	m	yes					x		x	x			x	x
7	Andrew Davis	80+	m	yes						x		x
8	Mia Allen	80+	f	yes							x	x		x
Frankfurt

Cells with Data Validation
Cell	Allow	Criteria
C1:C8	List	m;f

 

[pandwra14]

Mitarbeiter Stand 0189cc7efaecf475826e0e4e232d7587a0925914e1d34a9a9e3ae73eed71948679.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Name	Hours	Sex	Contract	Business email	Private email	Phone number	Comments	Training 1	Training 2	Training 3	Training 4	Training 5	Training 6	Training 7	Training 8
3	John Smith	80+	m	yes					x		x	x			x	x
4	Rebecca Anderson	80+	f	yes					x	x					x
5	Michael Brown	MJ	m	yes					x	x	x	x		x
6	Alex Martin	80+	m	yes					x		x	x			x	x
7	Andrew Davis	80+	m	yes						x		x
8	Mia Allen	80+	f	yes							x	x		x
Frankfurt

Cells with Data Validation
Cell	Allow	Criteria
C1:C8	List	m;f

Example: Row 3: IF Training 2,5,6 are green (only the ones without the cross), then the cells A3 to G3 turn green; IF Training 5 (key training) green/yellow (other trainings here don´t matter), then the cells A3 to G3 turn yellow (until all the needed trainings are green, then the cells also turn green); IF Training 5 is red, then cells A3 to G3 turn red (no matter the color of other required trainings). Another condition has to be hours column (B): IF Hours is "80+" like in Row 3, then cells A3 to G3 will have bright green/yellow/red colors; IF Hours is "MJ" like in Row 5, colors of A3 to G3 will be light green/yellow/red. I would be extremely grateful if you could find a solution for this! Thank you in advance!

 

[kevin9999]

I'm am sorry, but you have completely lost me with your clarification in post #8 of what the flow of logic is behind your colour scheme. I'm stepping out of this thread now and only hope that someone else on this forum with greater insight than me can come to your aid. Good luck and best wishes!

 

[Drrellik]

Hello,

Sound like you are trying to do a couple of things here.

Let me try to understand:

1. If J3, M3, & N3 are green then you want A3:G3 to be green
2. If M3 is Yellow then you want A3:g# to be yellow
3. If M3 is Red then A3:G3 is to be red

Also if B3 is 80+ use bright colors and if not use light colors.


Does that sound like what you are trying to do?


If so without using VBA and getting into something I don't know well, consider using a Nested IF statement to solve your arguments and conditional formatting.

in your training columns you can use 1=green, 2=yellow & 3=red or any number(s) or letters you want. and conditional formatting to turn both the cell interior color and the font color to G,Y,R as you see and also use conditional formatting to change the cell colors in A3:G3 to a color based on the 'Value' you put in 2,5,6