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-

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

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

Not with 5 values it's not.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"EdMac" wrote in
message ...

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

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

Ed


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=504334

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=