Macro

[Guest]

Hi,
I need help with starting off a macro, and hope someone can help. I'll try
and briefly describe what I am trying to achieve. The w/s below represents a
very simple project progress track for weekly presentation purposes. Each
project will be represented by a single line entry. One field will be
'Status' which can be: Green, Amber, Red, Blue or Yellow. The next field will
be 'Trend' which can be +, -, or =.

So, if I could get help developing the following I think I may be able to
have a go at the rest.Given w/s Project with columns A to E:

1. if 'g' (for Green) is input into any cell (X) in column D (Status)then:
- cell is 'filled' with color green,
- Color of any text input to E(X) is colored green,
2. Is there any way to track previous entry in D(X) such that if previous
entry in D(X) was 'g', while current status is 'g', then trend '=' is shown
in green color. However, if previous status was 'r' and current status is 'r'
then trend '=' is shown in red color.

Thx,

Don-

 

[Glen]

Sorry Don, I replied for this once before but it didn't take. Here
goes again

We're going to work with the green checker:

Dim gcheck as string
dim gflag as integer
dim aflag as integer
dim rflag as integer
'etc. to get the color coded flags

' set the flags to zero
gflag = 0
aflag = 0
rflag = 0

gcheck = Range("D(x)").value
If InStr(gcheck, "g") <> 0 then
gflag = gflag + 1
If gflag > 5 then ' this is assuming 5 will indicate a trend
Range("F(x)").select
With selection.font
.colorindex = 4
End With
End If
'Set the interior of D to green
Range("D(x)").select
With selection.interior
.colorindex = 4
End With
'Set the font in E to green
Range("E(x)").select
With selection.font
.colorindex = 4
End With
End If

I think that is everything. Hope that helps

 

[Glen]

HTH


Dim gcheck as string
Dim gflag as integer
Dim aflag as integer 'amberflag
dim rflag as integer ' red flag
' etc. making sure to set all flags to zero upon intialization
gcheck = range("D(x)").value
if InStr(gcheck, "g") <> 0 then
gflag = gflag + 1
aflag = 0
rflag = 0 'etc. to turn off other flags for trends
If gflag > 5 then ' if 5 proves a trend
Range("F(x)").value = "="
Range("F(x)").Select
With Selection.Interior
.ColorIndex = 4
'green
End With
End If

Range("D(x)").Select
With Selection.Interior
.ColorIndex = 4 'green
End With
Range("E(x)").Select
With Selection.Font
.Colorindex = 4
End With
End If