need to create a five-colour format macro for excel 2003. The conditons are:
B6=sum(B1:B5);
if B6<=55, then A1="Blue" (A1 filling should be dark blue and font should be
bold white);
if B6<=65, then A1="Green"(A1 filling should be dark green and font should
be bold white);
if B6<=75, then A1="Gold" (A1 filling should be gold and font should be bold
black);
if B6<=85, then A1="Extra" (A1 filling should be gray and font should be
bold black);
if B6<=100, then A1="Premiun" (A1 filling should be black and font should be
bold white);
Suggestions I was given so far haven't worked.
It sure would be helpful if you would let us know what the previous suggestions
were and what happened with the previous suggestions. What does "didn't work"
mean?
Computer crash? Program crash? Maybe you just didn't implement one of those
suggestions properly.
Here's one suggestion, but I have no idea if you've tried it before or not, or
even what version of Excel you are using.
If you are using Excel 2007, you can use the built-in conditional formatting.
Otherwise, you will need to use an event triggered macro.
To enter this, right click on the sheet tab and select "View Code"
Paste the code below into the window that opens.
You can tweak the RGB values for the non-primary colors, or use the colorindex
property.
Note the the RGB colors, according to the HELP screen, will not work on
Macintosh. I'd guess the actual values would, though, and they are in comments
after the RGB property.
===========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Range("A1")
Select Case Range("B6")
Case Is <= 55
.Value = "Blue"
.Interior.Color = vbBlue
.Font.Color = vbWhite
.Font.Bold = True
Case Is <= 65
.Value = "Green"
.Interior.Color = vbGreen
.Font.Color = vbWhite
.Font.Bold = True
Case Is <= 75
.Value = "Gold"
.Interior.Color = RGB(255, 204, 0) '52479
.Font.Color = vbBlack
.Font.Bold = True
Case Is <= 85
.Value = "Extra"
.Interior.Color = RGB(191, 191, 191) '12566272
.Font.Color = vbBlack
.Font.Bold = True
Case Is <= 100
.Value = "Premium"
.Interior.Color = vbBlack
.Font.Color = vbWhite
.Font.Bold = True
Case Else
.Clear
End Select
End With
Application.EnableEvents = True
End Sub
==============================
--ron