AP Conditional Formatting

[Antonio]

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.

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B6" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is <= 55:
With Me.Range("A1")
.Value = "Blue"
.Interior.ColorIndex = 5
.Font.Color = vbWhite
.Font.Bold = True
End With
Case Is <= 65:
With Me.Range("A1")
.Value = "Green"
.Interior.ColorIndex = 10
.Font.Color = vbWhite
.Font.Bold = True
End With
Case Is <= 75:
With Me.Range("A1")
.Value = "Gold"
.Interior.ColorIndex = 44
.Font.Color = vbBlack
.Font.Bold = True
End With
Case Is <= 85:
With Me.Range("A1")
.Value = "Extra"
.Interior.ColorIndex = 16
.Font.Color = vbBlack
.Font.Bold = True
End With
Case Is <= 100:
With Me.Range("A1")
.Value = "Premium"
.Interior.Color = vbBlack
.Font.Color = vbWhite
.Font.Bold = True
End With
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Bob,

In my limited testing, this doesn't work if Me.Range(WS_RANGE) contains a
formula, and the precedent cell is changed.

Is there some efficient method of handling this, rather than firing the macro
for every change?
--ron

 

[Bob Phillips]

I think you are right Ron, I only spotted the A1 twist at the end and added
it without testing.

Your approach is more robust.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)