Conditional Fill Macro
 

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

Conditional Fill Macro
 

You don't want code, you want conditional formatting. See Excel Help.

Nick Thorsch wrote:

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

Conditional Fill Macro
 

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

--
Message posted from http://www.ExcelForum.com

Conditional Fill Macro
 

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 TargetActual
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" wrote:

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

Conditional Fill Macro
 

YOU WERE RIGHT!! YOU ROCK JWOLF! THANKS:)
-----Original Message-----
You don't want code, you want conditional formatting.
See Excel Help.

Nick Thorsch wrote:

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
.

Conditional Fill Macro
 

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 TargetActual
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" wrote:

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

.

Conditional Fill Macro
 

Didn't quite work, Anson's did though so thanks for
trying :)

-----Original Message-----
Nick,

Here's some code I came up with. There are more ways to
do this
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 Sub


---
Message posted from http://www.ExcelForum.com/

.

Conditional Fill Macro
 

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)

"Nick Thorsch" wrote in message
...
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

Conditional Fill Macro
 

Nick,

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

Charle

--
Message posted from http://www.ExcelForum.com