Formatting Font colors via VB?

[Bob Smith]

Hi folks,

I have workbooks consisting of collecting and sorting players game in a
nationwide game, and each workbook deals with a certain set of sites
regionally. As an example, one of my workbooks for the Tampa metro area
consists of 38 worksheets, each sheet pulls down data from one site. I have
all the scores from each sheet copied via paste special to another
worksheet, where a macro sorts the scores on descending order.

From that page, I have what one would might call a master report sheet sent
out to the regional sites, where the sorted scores are plugged in via a
copy - paste special. The players can see where they stand with their point
totals on a regional basis. Long story short, everything works great. Each
player entry has three cells - Handle, Points earned & the Site they play
at.

Only thing is that there are different point levels which have different
font levels that I have to change manually, and I've heard that one can set
up VB to make those changes automatically. I know nothing about VB. I was
hoping someone here might start me off with the coding, which I can then
finish myself. Point levels are as follows:

From 12,000,000 and over - Yellow Font with a black background. Also has
white interior cell bordering
From 6,000,000 to 11,999,999 - Brown font, white/no background color, and
black cell boarding.
From 3,000,000 to 5,999,999 - Blue font, white/no background color, and
black cell boarding.
From 1,500,000 to 2,999,999 - White font with a black background. Also has
white interior cell boarding.
From 750,000 to 1,499,999 - Purple font with a white / no background. Also
has white interior cell boarding.

Oh, and btw, each point level block listed above is separated and bordered
by red cell boarding.

Can anyone start me off?

Much thanks. If you would like to see an example of that I'm talking about,
I can send you either the worksheet or a PDF of the page. My email addy is
usirsclt @ earthlink.net without spaces before & after the @.

Bob

 

[Frank Kabel]

Hi Bob

you may use an event procedure for this (have a look at
http://www.cpearson.com/excel/events.htm for more information about
them).
If you enter your points in column A the following could be a starting
point for you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Application.EnableEvents = False
Select Case .Value
Case 750000 to 1499999: .Interior.ColorIndex = 3
Case 1500000 to 2999999: .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub



Also see:
http://www.cpearson.com/excel/cformatting.htm
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[Bob Smith]

Hi Frank,

Thanks for the follow up. Unfortunately, it's a bit more complicated with 4
cells in row dedicated to one player. 150 players on one page in landscape
format. I hope you don't mind, but I've sent you an example to show what I'm
doing ...

Regards,Bob