Macro help required

[Guest]

Hi,
I am preparing a basic excel worksheet to present various projects using a 1
line (row) entry for each project. One cell in each row will hold a color
based 'status value', while the next cell will show trend.

I am using 4 colors to represent the status, and a +, - or = to represent
trend. So if trend is + it means a positive move in status since last
reporting period (eg status went from red to green, where red represents
serious issues while green represents everything on track). Up to this point
I have simply been using the conditional formatting. However, I think I need
to consider using macros as:
(a) I need an additional color in status (ie 5 colors) and,
(b) I want the trend symbol to be color coded (eg if status is green during
last reporting period, and there is no change, then trend = is green. However
if status is red from last reporting period, and there is no change, then
trend = is red.).

I am not entirely familiar with macro language but feel if I get a start I
will find my way .....
Could someone outline how I make a start to define macro:
- if user inputs a 'r' into 'status' cell (cell x), then the cell is turned
red (a red fill), and the 'r' font turns to red,
- if status is 'r' the trend (cell z) can only be - or =, and this should
follow the same color as status,
- if status is 'a' (amber) meaning minor issues, the trend cell can be
either = or +. If its = then follow same color as status (ie amber), if its +
then show as green.

I believe this should be pretty easy to code, but I haven't worked with
macros : (

Help greatly appreciated,

Thx,

Don-

 

[Bob Phillips]

I can only see 3 colours, not 5.

If status is amber, why can't trend be -, if previously green?

I would do it with CF on the status cell, just test the letter r,a, or g and
colour accordingly.

On the trend, I would use Data Validation with a formula of

=OR(AND(A21="r",OR(B21="-",B21="=")),AND(A21="a",OR(B21="-",B21="=",B21="+")
),AND(A21="g",OR(B21="+)",B21="=")))

assuming the trend cell is B21, and then use CF to colour with formulae of

=OR(A21="r",AND(A21="a",B21="-"))
=OR(A21="g",AND(A21="a",B21="+"))
=A21=""


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,

Thanks for reply. You atre correct re:amber (-was rushing this last night :
(..

There are in fact 5 colors:
green=ok, amber=minor issues, red = major issues, blue=completed/cancelled
and
we have another color (TBD) to address a specific state.

Does this change the complexion of your response?

Thx,

Don-