Conditional Fill Macro

N

Nick Thorsch

Hi,
I want visual basic code that will compare the first
cell's value, the goal, to the second cell's value, the
actual value. If the actual is greater than the goal, the
actual cell is filled in green. If the actual is less
than the goal, the actual cell is filled in red. The
process repeats down the two columns, comparing varying
actuals to goals and filling with green or red to create
a clearer managerial dashboard. The code I have created
so far might help get you started. Please post the
correct code. I have spent hours on this in frustration.
If you can solve this, please email it to me and THANK
YOU VERY MUCH!!

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/14/2004 by Nick Thorsch
'

'
If "K3" > "L3" Then
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Else
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End Sub
 
C

Charles

Nick,

Here's some code I came up with. There are more ways to do thi
though.

Charles

Sub colur_cells()
Application.ScreenUpdating = False
Dim rng As Range
Dim i As Long
Set rng = Worksheets("sheet1").Cells(1, 11).CurrentRegion
For i = 1 To rng.Rows.Count
If rng(i, 1).Value > rng(i, 2).Value Then
With rng(i, 1).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Else
With rng(i, 1).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next
End Su
 
A

Anson

Try below. I based it on the following assuptions:

List starts from [A1] and [B1] as headings
Column A is continous (required field)
Column A is your goal (target); Column B is your Actual

'--------------------------------------------------------
Sub ColourCoding()

Dim Pivot As Range
Dim iRow As Integer
Dim Colour As Integer

'Set pivot as the heading of the list
Set Pivot = ThisWorkbook.Sheets("Sheet1").[A1]

iRow = 1

'Run through each row on list
Do While IsEmpty(Pivot.Offset(iRow, 0)) = False
'Column 1 = Target; Column 2 = Actual
'Set Colour = 4 if Target>Actual
If Pivot.Offset(iRow, 0) > Pivot.Offset(iRow, 1) Then
Colour = 4

'Set Colour = 3 if Target<Actual
ElseIf Pivot.Offset(iRow, 0) < Pivot.Offset(iRow, 1) Then
Colour = 3

'Set Colour = Nothing if Target = Actual
Else
Colour = xlNone
End If

'Perform formatting
Pivot.Offset(iRow, 1).Interior.ColorIndex = Colour

'Next row
iRow = iRow + 1
Loop

End Sub
 
N

Nick Thorsch

ANSON YOU ROCK TOO! Works Beautifully. This newsgroup is
great!
-----Original Message-----
Try below. I based it on the following assuptions:

List starts from [A1] and [B1] as headings
Column A is continous (required field)
Column A is your goal (target); Column B is your Actual

'--------------------------------------------------------
Sub ColourCoding()

Dim Pivot As Range
Dim iRow As Integer
Dim Colour As Integer

'Set pivot as the heading of the list
Set Pivot = ThisWorkbook.Sheets("Sheet1").[A1]

iRow = 1

'Run through each row on list
Do While IsEmpty(Pivot.Offset(iRow, 0)) = False
'Column 1 = Target; Column 2 = Actual
'Set Colour = 4 if Target>Actual
If Pivot.Offset(iRow, 0) > Pivot.Offset(iRow, 1) Then
Colour = 4

'Set Colour = 3 if Target<Actual
ElseIf Pivot.Offset(iRow, 0) < Pivot.Offset(iRow, 1) Then
Colour = 3

'Set Colour = Nothing if Target = Actual
Else
Colour = xlNone
End If

'Perform formatting
Pivot.Offset(iRow, 1).Interior.ColorIndex = Colour

'Next row
iRow = iRow + 1
Loop

End Sub


Nick Thorsch said:
Hi,
I want visual basic code that will compare the first
cell's value, the goal, to the second cell's value, the
actual value. If the actual is greater than the goal, the
actual cell is filled in green. If the actual is less
than the goal, the actual cell is filled in red. The
process repeats down the two columns, comparing varying
actuals to goals and filling with green or red to create
a clearer managerial dashboard. The code I have created
so far might help get you started. Please post the
correct code. I have spent hours on this in frustration.
If you can solve this, please email it to me and THANK
YOU VERY MUCH!!

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/14/2004 by Nick Thorsch
'

'
If "K3" > "L3" Then
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Else
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End Sub
Click to expand...
.
Click to expand...
 
B

Bob Phillips

Sub Macro1()

For i = 3 To 100
With Cellls(i,"K")
If .Value < .Offset(0,1).Value Then
With .Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else
With .Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
Next i
End Sub


but you should be using conditional formatting.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
C

Charles

Nick,

Re tested my version. It's based on the values being in column "K" an
"L"

Charle
 
Top