Conditional Formatting Not Working

[VinceF]

Another one for the experts. I'm trying to apply conditional formatting using the format painter but for whatever reason it doesn't seem to be Appling the condition as it should.
I have 9 columns Z5 thru AH5 and 39 rows J12 thru J51
The formula that I'm using is =AND($B$7="Half","Half Stroke Skins"!,$Z5=1)) when I drag this across with FP it doesn't hold the formatting correctly. I then manually corrected each cell in the row 5 to this...=AND($B$7="Half","Half Stoke Skins"!,Z$5=1)) and tried dragging it down, same problem.

I hope that I've explained this adequately and thank you for your assistance.

VinceF
Win 10
Office 2016

 

[etaf]

which range are you formatting - when you select a range to format - that is the start of the formula
so if you start at Z2 in the selection , then you need to start the formula at Z2 to test that cell
OR
you can use FIXED address with the $
so
$B$7
Will always look at that cell nomatter where you are or where you drag

$Z5=1
Will always look at column Z as that has the $
BUT as you drag down the rows, that will change - so Z6,Z7 etc

however
Z$5=1
Fixes the row
So will always look in Row 1
and as you drag
Z5, AA5,AB5,AC5, across or down

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[VinceF]

which range are you formatting - when you select a range to format - that is the start of the formula
so if you start at Z2 in the selection , then you need to start the formula at Z2 to test that cell
OR
you can use FIXED address with the $
so
$B$7
Will always look at that cell nomatter where you are or where you drag

$Z5=1
Will always look at column Z as that has the $
BUT as you drag down the rows, that will change - so Z6,Z7 etc

however
Z$5=1
Fixes the row
So will always look in Row 1
and as you drag
Z5, AA5,AB5,AC5, across or down

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Etaf,

Firstly, thank you for your reply.

You'll see on the Main page J12 is highlighted in Red. The CF is looking at a helper sheet called "Half Stroke Skins". The CF in J12 I believe is set up correctly to drag the format painter from left to right which is looking at cell Z5 thru AH5 on the helper sheet. Once that is done, I'd fix each cell to be dragged down by the FP by moving the $ appropriately. I've tried this many times and as I indicated the FP isn't referencing the cells as it should be. My only alternative is to do each cell manually...but that'd take a month of Sunday's.

MASTER TEMPLET skins-stableford sheet 2-27-22.xlsm
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
11	SKINS	FRONT									IN	BACK									OUT	TOTALS	POINTS
12	Yes	3	4	4	4	4	4	4	4	4	35	4	5	5	5	5	5	5	5	5	44	79	28
13	Yes	5	3	4	4	4	4	4	4	4	36	5	3	5	5	5	5	5	5	5	43	79	28
14	Yes	5	4	3	4	4	4	4	4	4	36	5	5	4	5	5	5	5	5	5	44	80	27
15	Yes	5	4	5	3	5	5	5	5	5	42	5	5	5	4	5	5	5	5	5	44	86	21
16	Yes	5	5	5	4	3	5	5	5	5	42	5	5	5	5	4	5	5	5	5	44	86	21
17	Yes	5	5	5	5	5	3	5	5	5	43	5	5	5	5	5	4	5	5	5	44	87	20
18	Yes	5	5	5	5	5	4	3	5	5	42	5	5	5	5	5	5	4	5	5	44	86	21
19	Yes	5	5	5	5	5	5	5	3	5	43	5	5	5	5	5	5	5	4	5	44	87	20
20	Yes	5	5	5	5	5	5	5	5	3	43	5	5	5	5	5	5	5	5	3	43	86	21
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J12	Expression	=AND($B$7="Half",'Half Stroke Skins'!Z$5=1)	text	NO
J12	Expression	=AND($B$7="Full",' Full Stroke Skins'!Z$5=1)	text	NO
I11	Expression	=($B$4="skins")	text	NO
I11	Expression	=($B$4="stableford")	text	NO
I11	Expression	=ISTEXT($I$12:$I$51)	text	NO
I11	Expression	=ISNUMBER($D$12:$D$51)	text	NO
I11	Expression	=$B$4="Stableford"	text	YES

 

[etaf]

so J12 is
=AND($B$7="Half",'Half Stroke Skins'!Z$5=1)
and will highlight = IF $B$7="HALF" and the helper sheet Z5 =1
OR as you have a second formula for RED fill
=AND($B$7="Full",' Full Stroke Skins'!Z$5=1)
Then B7 =full
therefore
IF Z5 on helper =1 , and B7 is either FULL or HALF - it will be red

lets move now to K13 cell
Because the formula has B7 with $ that will not change
so B7 is either FULL or HALF - it will be red

BUT Z5 is fixed with a $5
so the ROW cannot change
as we have moved down a row to K13 - it still checks row 5 on your helper column
BUT the column can move - so now its checking AA
so the formulas effectively being used in that K13 cell to turn the fill RED is
=AND($B$7="Full",' Full Stroke Skins'!AA$5=1)
=AND($B$7="HALF",' Full Stroke Skins'!AA$5=1)

perhaps also post the helper sheet using xl2bb

 

[VinceF]

Hope this helps...

MASTER TEMPLET skins-stableford sheet 2-27-22.xlsm
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2	HOLE	1	2	3	4	5	6	7	8	9
3	HDCP	7	17	1	5	15	9	13	11	3
4	PAR	5	3	4	4	3	4	4	4	4
5		1	0	0	0	0	0	0	0	0
6		0	1	0	0	0	0	0	0	0
7		0	0	1	0	0	0	0	0	0
8		0	0	0	1	0	0	0	0	0
9		0	0	0	0	1	0	0	0	0
10		0	0	0	0	0	1	0	0	0
11		0	0	0	0	0	0	1	0	0
12		0	0	0	0	0	0	0	1	0
13		0	0	0	0	0	0	0	0	1
14		0	0	0	0	0	0	0	0	0
15		0	0	0	0	0	0	0	0	0
16		0	0	0	0	0	0	0	0	0
17		0	0	0	0	0	0	0	0	0
18		0	0	0	0	0	0	0	0	0
19		0	0	0	0	0	0	0	0	0
20		0	0	0	0	0	0	0	0	0
21		0	0	0	0	0	0	0	0	0
22		0	0	0	0	0	0	0	0	0
23		0	0	0	0	0	0	0	0	0
24		0	0	0	0	0	0	0	0	0
25		0	0	0	0	0	0	0	0	0
26		0	0	0	0	0	0	0	0	0
27		0	0	0	0	0	0	0	0	0
28		0	0	0	0	0	0	0	0	0
29		0	0	0	0	0	0	0	0	0
30		0	0	0	0	0	0	0	0	0
31		0	0	0	0	0	0	0	0	0
32		0	0	0	0	0	0	0	0	0
33		0	0	0	0	0	0	0	0	0
34		0	0	0	0	0	0	0	0	0
35		0	0	0	0	0	0	0	0	0
36		0	0	0	0	0	0	0	0	0
37		0	0	0	0	0	0	0	0	0
38		0	0	0	0	0	0	0	0	0
39		0	0	0	0	0	0	0	0	0
40		0	0	0	0	0	0	0	0	0
41		0	0	0	0	0	0	0	0	0
42		0	0	0	0	0	0	0	0	0
43		0	0	0	0	0	0	0	0	0
44		0	0	0	0	0	0	0	0	0
Half Stroke Skins

 

[etaf]

ONLY Z5 =1 , so thats the only cell that will highlight, on the main sheet is J12
IF you change some of the row 5 cells to a 1 - then the other sheet cells should also highlight red

J = Z on helper
K = AA on helper

for example changing cell AF5 to a 1
then P12 on the main sheet should fill RED

 

[VinceF]

I had cleared out the CF's from all of the cells except for J12...I'll fill them in using the Format painter so that you can see what it is or what it is not doing....give me a few.

 

[VinceF]

I had cleared out the CF's from all of the cells except for J12...I'll fill them in using the Format painter so that you can see what it is or what it is not doing....give me a few.

Ok...I ran J12 across using the FB, then ran the rows down using the FP, this is what I got.

MASTER TEMPLET skins-stableford sheet 2-27-22.xlsm
	J	K	L	M	N	O	P	Q	R
12	3	4	4	4	4	4	4	4	4
13	5	3	4	4	4	4	4	4	4
14	5	4	3	4	4	4	4	4	4
15	5	4	5	3	5	5	5	5	5
16	5	5	5	4	3	5	5	5	5
17	5	5	5	5	5	3	5	5	5
18	5	5	5	5	5	4	3	5	5
19	5	5	5	5	5	5	5	3	5
20	5	5	5	5	5	5	5	5	3
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K13:R51	Expression	=AND($B$7="half",'Half Stroke Skins'!$AA6=1)	text	NO
K13:R51	Expression	=AND($B$7="full",' Full Stroke Skins'!$AA6=1)	text	NO
J13:J51	Expression	=AND($B$7="half",'Half Stroke Skins'!Z$5=1)	text	NO
J13:J51	Expression	=AND($B$7="full",' Full Stroke Skins'!Z$5=1)	text	NO
K12:R12	Expression	=AND($B$7="half",'Half Stroke Skins'!$AA5=1)	text	NO
K12:R12	Expression	=AND($B$7="full",' Full Stroke Skins'!$AA5=1)	text	NO
J12	Expression	=AND($B$7="half",'Half Stroke Skins'!Z$5=1)	text	NO
J12	Expression	=AND($B$7="full",' Full Stroke Skins'!Z$5=1)	text	NO

 

[etaf]

it will highlight down the column with the format painter

As the formula
=AND($B$7="Half",'Half Stroke Skins'!Z$5=1)
is not changing in any of those cells

but you have loads of different formulas now with format painter
applying to different ranges

Why use the format painter brush - any particular reason - then just select all the cells you want to apply the conditional formatting to
and then add the formula(s)

then you will just get the one formula appling across the whole sheet - instead of lots of formulas for different ranges to do the same formula

Book5
	J	K	L	M	N	O	P	Q	R	S
1	SKINS	FRONT
2	Yes	3	4	4	4	4	4	4	4	4
3	Yes	5	3	4	4	4	4	4	4	4
4	Yes	5	4	3	4	4	4	4	4	4
5	Yes	5	4	5	3	5	5	5	5	5
6	Yes	5	5	5	4	3	5	5	5	5
7	Yes	5	5	5	5	5	3	5	5	5
8	Yes	5	5	5	5	5	4	3	5	5
9	Yes	5	5	5	5	5	5	5	3	5
10	Yes	5	5	5	5	5	5	5	5	3
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:AC10	Expression	=AND($B$7="Full",'Half Stroke Skins'!AA$5=1)	text	NO

 

[VinceF]

ok thank you let me play with it a bit and I'll let you know...very much appreciate the assistance.