Conditional Formating

[PL dub]

Fill in RED

=OR(AND(B4<4,WEEKDAY(A4,2)>5),AND(B4<6,WEEKDAY(A4,2)<6))

Fill in Green

=OR(AND(B4>3,WEEKDAY(A4,2)>5),AND(B4>5,WEEKDAY(A4,2)<6))

I need some help with the above code. I use these formula in conditional formating.

Basically what it does, Value in B4 is the number of person working at a specific date. This specific date is in A4.
Color Fill cell B4 in RED when below minimum staff
Color Fill cell B4 in Green when Equal or above minimum staff

Minimum staff may differ if the specific date is during weekend.

My question :
I would like to apply this formula on cell B4 to B34 where the specific date is always on the same line. So B30 should use A30 as specific date.

I'm sure there is a way to do this, I'm just not there yet.
Thank in advance for you help.

 

[jorismoerings]

Hi,

Just enter these formula's into a conditional formatting formula related to Cell B4 and make sure you don't include any absolute reference (so no dollar signs in the formula).
From there are 2 ways to go:
1. in the conditional formatting input box; select B4:B34 into the Apply to box.
2. just copy Cell B4 to B5 and down till B34.

In both cases Excel will automatically change the cell references.

 

[PL dub]

Didn't thought it would also work for the conditional formating, but it does.

Thanks !

 

[ockyoz]

Hi I have an issue with conditional formatting, basically I have a spreadsheet with a few tabs, one looking like this:

aaa333
abc123
bbb111
bca111
ccc222
cde435
ddd334
ddd444
fsd242
hhh444
hht344
hmk343
mdf234
ooo344
qwe555
sfg767
the464
try323
tty789
yme785
zsd564

and I need to highlight each of these codes with a different colour based on a few lists in a different tab:

aaa333	ddd334	hhh444
abc123	fsd242	ooo344
bbb111	mdf234	hht344
bca111	yme785	tty789
ccc222	sfg767	the464
ddd444	hmk343	cde435
qwe555	zsd564	try323

<tbody>
</tbody>

Say any matches from the first column to go yellow, second column red and third in blue and so on.
I've tried =ISNUMBER(MATCH(yellow,A1,0)) but it doesnt quite work. Yellow btw is the name of the range of codes from the first column.

Any help will be greatly appreciated.
Cheers