Mass formatting issues

chinchi81

Board Regular
Joined
Jul 13, 2007
Messages
69
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
2DateHomeAwayFTHGFTAGFTRHTHGHTAGHTRHDA51GoalsHDA17GoalsHDA7GoalsWHHWHDWHA51177
303/08/2007MechelenAnderlecht01A00DA-1.02A-2.18A-1.715.53.51.534.54.54.5AAA
404/08/2007CharleroiRoeselare11D10HH2.00H3.291.623.45-1-1-1HH
504/08/2007ClubBruggeBergen21H11DH2.65H2.47H2.001.3647.50.360.360.36HHH
604/08/2007DenderGerminal12A00D2.883.22.2-1-1-1 
704/08/2007FCBrusselsWesterlo01A00DA0.76D0.94H1.712.23.22.881.2-1-1ADH
804/08/2007GenkCercleBrugge31H31HH2.31H2.24D1.001.3348.50.330.33-1HHD
2007-2008
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Tank997

Board Regular
Joined
Feb 12, 2005
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 12, 2005
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 12, 2005
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 13, 2007
Messages
69

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jul 13, 2007
Messages
69

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Feb 12, 2005
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top