How to apply changing conditional formatting to an array of columns

[Jaylo801]

Hello,
I am trying to apply conditional formatting to an array of data. Specifically, if data in a specific column g7:g87 is equal to the text value in g5, I want it to be green with green writing. I can easily do this for this one column. But actually I would like the same formatting to apply to columns H to AT, with the row numbers staying the same, while the column changes, so that the formatting applies to each individual column. Of course I can do this formatting operation 40 times but I'm hoping there is a quicker and easier way to do this.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

1. Remove that existing Conditional Formatting
2. Select G7:AT87
3. Conditional Formatting -> New rule .. -> Use a formula to determine which cells to format -> Format values where this formula is true: =$G7=$G$5 -> Format .. -> choose your formatting -> OK -> OK

 

[Jaylo801]

Welcome to the MrExcel board!

Try this

1. Remove that existing Conditional Formatting
2. Select G7:AT87
3. Conditional Formatting -> New rule .. -> Use a formula to determine which cells to format -> Format values where this formula is true: =$G7=$G$5 -> Format .. -> choose your formatting -> OK -> OK

Thanks, that is moving in the right direction but maybe needs a tweak - now if the rule is true for the first column, it has applied across the rows. Would it be better to be =$g7=$g5

 

[Jaylo801]

Thanks, that is moving in the right direction but maybe needs a tweak - now if the rule is true for the first column, it has applied across the rows. Would it be better to be =$g7=$g5

Actually that doesn't seem to work either. Here's what I have now, when I want the cell to be green if it matches the yellow cell, but the text value in the yellow cell changes for each column.

 

[Peter_SSs]

Sorry, I mis-interpreted what you wanted. Try the steps above again but with this formula.

=G7=G$5

 

[Jaylo801]

Sorry, I mis-interpreted what you wanted. Try the steps above again but with this formula.

=G7=G$5

Thank you that does seem to work. My only issue now is that it has not applied to all of the columns. It worked G-K and P-R, T, AD, AM, and AQ but not the other columns. I'm guessing that is not a problem with the formula but something else?

 

[Peter_SSs]

Did you follow the steps exactly?

Can you post a small section of the sheet, including some columns that are not working as expected with XL2BB instead of just an image? That way we should be able to work out what the issue is.

Here is a small section of my dummy worksheet

24 02 19.xlsm
	G	H	I	J	K	L	M	N	O
5	a	b	c	d	e	f	g	h	i
6	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9
7	a	b	c	d	e	f
8	b							h	i
9	c			e	e
10	d			e	e
11	a
12	c
13	a
14	a
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G7:AT87	Expression	=G7=G$5	text	NO

 

[Jaylo801]

Reading Power Up 2024 BEFORE Test .xlsx
	G	H	I	J	K	L	M	N	O	P
5	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.	C. confident.	D. to cook the damper without burning herself
6	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A.Emu.	B.She had an itchy rash on her skin.	B.It helped her to be brave.	C.confident.	D. to cook the damper without burning herself
7	B. electricity	C. the copper and icebox.	B. letter.	B. the sea	C. poured	A. Emu.	B. She had an itchy rash on her skin.	D. She wanted Emu Oil on her leg.	B. worried.	C. to watch it hover over the coals
8	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.	B. worried.	A. to set it on fire
9	C. wood fires	D. lights and radios.	B. letter.	B. the sea	C. poured	C. Milky Way.	C. She was born with a stiff, weak leg.	A. It made her cry in bed.	B. worried.	B. to poke the fire with it
10	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.	C. confident.	D. to cook the damper without burning herself
11	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.	C. confident.	D. to cook the damper without burning herself
12	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.	C. confident.	D. to cook the damper without burning herself
13	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	A. It made her cry in bed.	A. angry.	A. to set it on fire
14	C. wood fires	D. lights and radios.	B. letter.	D. the environment	B. stirred	B. Emu Oil.	A. She had broken a bone.	B. It helped her to be brave.	B. worried.	A. to set it on fire
15	C. wood fires	D. lights and radios.	B. letter.	B. the sea	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.	C. confident.	C. to watch it hover over the coals
16	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	C. She was born with a stiff, weak leg.	B. It helped her to be brave.	C. confident.	B. to poke the fire with it
17	C. wood fires	C. the copper and icebox.	B. letter.	D. the environment	D. rubbed	A. Emu.	B. She had an itchy rash on her skin.	D. She wanted Emu Oil on her leg.	B. worried.	B. to poke the fire with it
18	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	B. She had an itchy rash on her skin.	D. She wanted Emu Oil on her leg.	B. worried.	B. to poke the fire with it
19	B. electricity	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.	C. confident.	B. to poke the fire with it
20	C. wood fires	C. the copper and icebox.	B. letter.	B. the sea	B. stirred	B. Emu Oil.	A. She had broken a bone.	A. It made her cry in bed.	B. worried.	C. to watch it hover over the coals
21	C. wood fires	B. fridges and washing machines.	B. letter.	D. the environment	B. stirred	B. Emu Oil.	D. She wanted to run like Cathy Freeman.	B. It helped her to be brave.	A. angry.	A. to set it on fire
Stage 3 test LEVELS

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G7:AT87	Expression	=G7=G$5	text	NO

Hope that works - I put a section of the sheet with the columns that appeared as I hoped and some columns that were not following the conditional formatting.

 

[Peter_SSs]

Thanks for that. Seems to be working from what I can see. I assume that you are wondering about cells like L7?
L7 is not the same as L5 because L7 has a lot of spaces between A. and Emu whereas L5 does not. Therefore L7 is not formatted.

Best thing would be to tidy the actual data, but if you are forced to work with the data as it is, perhaps this would suffice.

24 02 19.xlsm
	G	H	I	J	K	L	M	N
5	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.
6	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A.Emu.	B.She had an itchy rash on her skin.	B.It helped her to be brave.
7	B. electricity	C. the copper and icebox.	B. letter.	B. the sea	C. poured	A. Emu.	B. She had an itchy rash on her skin.	D. She wanted Emu Oil on her leg.
8	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.
9	C. wood fires	D. lights and radios.	B. letter.	B. the sea	C. poured	C. Milky Way.	C. She was born with a stiff, weak leg.	A. It made her cry in bed.
10	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.
11	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.
12	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G7:P21	Expression	=TRIM(G7)=TRIM(G$5)	text	NO

 

[Peter_SSs]

Or if this is really all about the correct multiple choice options then perhaps this which just checks the first character.

24 02 19.xlsm
	G	H	I	J	K	L	M	N
5	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.
6	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A.Emu.	B.She had an itchy rash on her skin.	B.It helped her to be brave.
7	B. electricity	C. the copper and icebox.	B. letter.	B. the sea	C. poured	A. Emu.	B. She had an itchy rash on her skin.	D. She wanted Emu Oil on her leg.
8	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.
9	C. wood fires	D. lights and radios.	B. letter.	B. the sea	C. poured	C. Milky Way.	C. She was born with a stiff, weak leg.	A. It made her cry in bed.
10	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.
11	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	D. Great Emu.	B. She had an itchy rash on her skin.	B. It helped her to be brave.
12	C. wood fires	D. lights and radios.	B. letter.	D. the environment	D. rubbed	A. Emu.	A. She had broken a bone.	B. It helped her to be brave.
CF (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G7:P21	Expression	=LEFT(G7,1) = LEFT(G$5,1)	text	NO