Macro help required

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-

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)

"Don" <[email protected]> wrote in message
news:[email protected]...
> 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,

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-

"Bob Phillips" wrote:

> 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)
>
> "Don" <[email protected]> wrote in message
> news:[email protected]...
> > 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-
> >
>
>
>