Adding colour to result in a cell?

[Pheasant Plucker®]

Hi there,

I am developing a spreadsheet that uses data validation and a chooser box to
select Y or N and depending upon the result drops a value in the adjacent
box.

This works as expected and depending upon the formula will enter a number
from 1-5 when N is selected from the chooser.

I would like to allocate different colours to result, i.e.

If Y is selected I would like the Y to appear in green in the chooser to
indicate an OK result. In this case there is no value dropped into the
adjacent box when Y is chosen.

If N is selected I would like the N to appear in Red in the chooser and the
result that is dropped in the adjacent box to be coloured accordingly.

For example if 5 is the value then this should be displayed in Red

If a 1 is the result than display this in Cyan etc.

As an example the formula I use to return the result is based upon the
following;

=IF(E19="N",1,"")

Is this possible please?

--
Thanks & regards,
-pp-

[Bob Phillips]

This should get you started. It assumes the DV box is E5


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("E5, E19")) Is Nothing Then
If Range("E5").Value = "Y" Then
Select Case Range("E19").Value
Case 1: Range("E19").Interior.ColorIndex = 1
Case 2: Range("E19").Interior.ColorIndex = 2
Case 3: Range("E19").Interior.ColorIndex = 3
Case 4: Range("E19").Interior.ColorIndex = 4
Case 5: Range("E19").Interior.ColorIndex = 5
Case Else: Range("E19").Interior.ColorIndex = 10
End Select
Else
Select Case Range("E19").Value
Case 1: Range("E19").Interior.ColorIndex = 21
Case 2: Range("E19").Interior.ColorIndex = 22
Case 3: Range("E19").Interior.ColorIndex = 23
Case 4: Range("E19").Interior.ColorIndex = 24
Case 5: Range("E19").Interior.ColorIndex = 8
Case Else: Range("E19").Interior.ColorIndex = 3
End Select
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH


Bob Phillips

(remove nothere from the email address if mailing direct)

"Pheasant PluckerŪ" <pheasant@plucker.not> wrote in message
news:ubjrDlLIGHA.344@TK2MSFTNGP11.phx.gbl...
> Hi there,
>
> I am developing a spreadsheet that uses data validation and a chooser box
to
> select Y or N and depending upon the result drops a value in the adjacent
> box.
>
> This works as expected and depending upon the formula will enter a number
> from 1-5 when N is selected from the chooser.
>
> I would like to allocate different colours to result, i.e.
>
> If Y is selected I would like the Y to appear in green in the chooser to
> indicate an OK result. In this case there is no value dropped into the
> adjacent box when Y is chosen.
>
> If N is selected I would like the N to appear in Red in the chooser and
the
> result that is dropped in the adjacent box to be coloured accordingly.
>
> For example if 5 is the value then this should be displayed in Red
>
> If a 1 is the result than display this in Cyan etc.
>
> As an example the formula I use to return the result is based upon the
> following;
>
> =IF(E19="N",1,"")
>
> Is this possible please?
>
> --
> Thanks & regards,
> -pp-
>
>

[EdMac]

If you are not into VBA coding the easiest way to do this is with conditional formatting.

Ed

[Bob Phillips]

Not with 5 values it's not.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"EdMac" <EdMac.224tez_1138094102.7414@excelforum-nospam.com> wrote in
message news:EdMac.224tez_1138094102.7414@excelforum-nospam.com...
>
> If you are not into VBA coding the easiest way to do this is with
> conditional formatting.
>
> Ed
>
>
> --
> EdMac
> ------------------------------------------------------------------------
> EdMac's Profile:
http://www.excelforum.com/member.php...o&userid=30736
> View this thread: http://www.excelforum.com/showthread...hreadid=504334
>

[EdMac]

Agreed,Bob, but not clear exactly what is required.

Ed

[Pheasant Plucker®]

Thanks for the replies Bob & Ed,

That first reply from you Bob nearly blew my mind...where on earth does that
lot go?

Conditional formatting sounds like it might be easier - if I were to reduce
the number of colours needed from 5 down to 3 or even 2 how would
conditional formatting work?

An example or two would be nice - please excuse my ignorance and bear with
me - I am a complete idiot when it comes to Excel (and many other things
besides! :-)

Thanks & regards,
-=pp=