Mass formatting issues

[chinchi81]

Hi,

I have the below spreadsheet and require to format it depending on what appears in column AE and column N. So for example if in AE it says AAA and the figure in column N is >=-1 and<-1.1, then on that row, cells A to C, J to N & cell AE would change format to a black background and white font.

This would need to happen from rows 3 to 308.

As I have more than 3 conditions, I will need to put this in a macro but am not sure as to how to get it started.

Belgium - Jupiler League.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
2	Date	Home	Away	FTHG	FTAG	FTR	HTHG	HTAG	HTR	H	D	A	51	Goals	H	D	A	17	Goals	H	D	A	7	Goals	WHH	WHD	WHA	51	17	7
3	03/08/2007	Mechelen	Anderlecht	0	1	A	0	0	D				A	-1.02				A	-2.18				A	-1.71	5.5	3.5	1.53	4.5	4.5	4.5	AAA
4	04/08/2007	Charleroi	Roeselare	1	1	D	1	0	H									H	2.00				H	3.29	1.62	3.4	5	-1	-1	-1	HH
5	04/08/2007	ClubBrugge	Bergen	2	1	H	1	1	D				H	2.65				H	2.47				H	2.00	1.36	4	7.5	0.36	0.36	0.36	HHH
6	04/08/2007	Dender	Germinal	1	2	A	0	0	D																2.88	3.2	2.2	-1	-1	-1
7	04/08/2007	FCBrussels	Westerlo	0	1	A	0	0	D				A	0.76				D	0.94				H	1.71	2.2	3.2	2.88	1.2	-1	-1	ADH
8	04/08/2007	Genk	CercleBrugge	3	1	H	3	1	H				H	2.31				H	2.24				D	1.00	1.33	4	8.5	0.33	0.33	-1	HHD
2007-2008

 

[Tank997]

You can use conditional formatting to change the cells you want to have the black background and white text.

Change the "Cell value" is to "Formula is" in the conditional fromatting window and paste this formula.

=IF(OR($AE4="AAA",$N4>=-1,$N4<=-1.1),TRUE,FALSE)

then click the format button, on the font tab set the text color to white and on the Patterns tab set the background color to black and click OK.

Good luck

 

[Tank997]

Sorry, that formula works if either condition is true and not, only if both conditions are true. I tried the "AND" function but I can't seam to get that to work.

 

[Tank997]

OK I think I have it try this formula in the conditional format.

=IF(AND($AE4="AAA",OR($N4>=-1,$N4<=-1.1)),TRUE,FALSE)

Also you can use the format painter to copy this, from the first cell you put it in, and paste it to any cells you want.

 

[chinchi81]

Hi Tom,

Thanks for that but I have more than 3 other conditions, for this spreadsheet alone I have 22 different ones, therefore I need to work this out in a macro.

Cheers,
Phil

 

[Smitty]

Welcome to the Board!

...but I have more than 3 other conditions, for this spreadsheet alone I have 22 different ones, therefore I need to work this out in a macro.

What have you tried so far?

What is the criteria for the formatting to be applied? In your case it seems to be the result of a formula change that's caused when G changes manually?

What you want can be probably done with a Change event, but you'll need to define the criteria, the formatting for each and the range to apply it to.

Here's the basis for a change event to get you started:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("G3:G308")
        <SPAN style="color:#007F00">'   Only accept single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Specify action to take</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[chinchi81]

Hi Smitty,

Thanks for the welcome. I should have been a bit clearer before. Column AE is just a composite of the data contained in cells M, R & W. In terms of the description before, I would also need it to change format if the values are between 0.9 and 1, 0.5 and 0.6, etc. There are also other different values for HHH, HHD, etc.

 

[Smitty]

If you really have 22 different conditions to format, then I'd suggest putting together a formatting table that details the range(s) to evaluate, the condition to be evaluated and the corresponding formatting to be applied and the range(s) to apply it to...

With that information we can start on a Select Case argument for you.

Smitty

 

[Tank997]

Phil, sorry I did not read the last sentence correctly and jumped to a conclusion that I thought would work. I have been working on Excel 2007 for almost a year now and forgot that older versions don’t support more than two conditional formats.

I am always intimidate about posting here because I know there are excel experts that know far more than I do, that can answer your questions, but sometimes I see posts that have a question where I have faced a similar problem lately, and I try to help.

I wish I know more about VBA! But my knowledge is limited to what I have learned by recording Macros and editing them.

I guess my question to the board is, is there a good DVD video training program out there that would teach the VBA code for Excel?

I work 60+ hours a week and would need something I can learn on my own time.

Any suggestions would be greatly appreciated!

 

[Smitty]

Phil, sorry I did not read the last sentence correctly and jumped to a conclusion that I thought would work. I have been working on Excel 2007 for almost a year now and forgot that older versions don’t support more than two conditional formats.

I am always intimidate about posting here because I know there are excel experts that know far more than I do, that can answer your questions, but sometimes I see posts that have a question where I have faced a similar problem lately, and I try to help.

I wish I know more about VBA! But my knowledge is limited to what I have learned by recording Macros and editing them.

I guess my question to the board is, is there a good DVD video training program out there that would teach the VBA code for Excel?

I work 60+ hours a week and would need something I can learn on my own time.

Any suggestions would be greatly appreciated!

Tank,

Never feel intimidated to ask a question here! That's what the board is for!

And even the "experts" learn a thing or two here, I know I am constantly picking things up. Excel is not a trivial piece of software and has tons of areas in which you can be competent, VBA is just one of them.

As for learning VBA, a Google search for "free excel VBA tutorials" will turn up a lot, you can also search the MSKB for "VBA Programmers Reference".

And there are several good books on the subject, not least of which by Bill Jelen (aka MrExcel), that you can find here.

HTH,

Smitty