Prev: Time Spent
Next: VLookup in VBA Help Needed
From: RedDevil on 8 Apr 2010 14:51
Hello
I have to format cells (I4:J37) if cells (I43:J76) are:
100%-91% (green)
90%-76% (blue)
75%-50% (yellow)
<50% (red)

How do I write that in VBA? (I am new to VBA so any help would be appreciated)

Many thanks
Tracey
From: Wouter HM on 8 Apr 2010 15:19
Hi Tracey,

I asume your users will enter a value in the range("I43:J76").

In that case you case use the Worksheet_change(Byval Target as Range
function like so:

Private Sub Worksheet_Change(ByVal Target As Range)
' Check if only 1 cells value is changed
If Target.Cells.Count > 1 Then Exit Sub

' Check if changed cell has a value
If IsEmpty(Target) Then Exit Sub

' Check if changed cell has numeric value
If Not IsNumeric(Target.Value) Then
' Change font color
Target.Font.Color = vbRed
Exit Sub
Else
Target.Font.Color = vbBlack
End If
' Check if changed cell is specific given range
If Intersect(Target, Range("I43:J76")) Is Nothing Then Exit Sub

Select Case Target.Value
Case 0.91 To 1
Target.Offset(-37, 0).Interior.Color = vbGreen
Case 0.76 To 0.91
Target.Offset(-37, 0).Interior.Color = vbBlue
Case 0.5 To 0.76
Target.Offset(-37, 0).Interior.Color = vbYellow
Case Else
Target.Offset(-37, 0).Interior.Color = vbRed
End Select
End Sub


HTH,

Wouter

From: ker_01 on 8 Apr 2010 15:34
Open the VBE, insert a new module, and paste in the code. Then select and run
it.

Note that this is not dynamic like true "conditional formatting"; e.g. if
the cell values change, the colors will not change until the macro is run
again.

HTH,
Keith

Sub UpdateColors()

For i = 1 To 34

RowToFormat = i + 3
RowOfData = i + 42
'Sheet1.Range("A1").Interior.Color

DataValue = Sheets("Sheet1").Range("I" & RowOfData).Value

Select Case DataValue
Case Is < 0.5
Sheets("Sheet1").Range("I" & RowToFormat).Interior.Color = vbRed
Case Is <= 0.75
Sheets("Sheet1").Range("I" & RowToFormat).Interior.Color =
vbYellow
Case Is <= 0.9
Sheets("Sheet1").Range("I" & RowToFormat).Interior.Color = vbBlue
Case Is <= 1
Sheets("Sheet1").Range("I" & RowToFormat).Interior.Color = vbGreen
Case Else
Sheets("Sheet1").Range("I" & RowToFormat).Interior.Color =
xlAutomatic
End Select

Next

End Sub

"RedDevil" wrote:

> Hello
> I have to format cells (I4:J37) if cells (I43:J76) are:
> 100%-91% (green)
> 90%-76% (blue)
> 75%-50% (yellow)
> <50% (red)
>
> How do I write that in VBA? (I am new to VBA so any help would be appreciated)
>
> Many thanks
> Tracey
 | 
Pages: 1
Prev: Time Spent
Next: VLookup in VBA Help Needed