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=

I did explain at the end how to implement it. If you want CF

Select all the cells, starting at let's say A2

Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add formula of =AND(E5="Y",E19=1)
Click Format
Select Pattern tab
Select red
OK

Click Add
Change Condition 1 to Formula Is
Add formula of =AND(E5="Y",E19=2)
Click Format
Select Pattern tab
Select green
OK

OK

obviously change the cells to suit.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

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

Bob's reply required you to be able to use VBA - that is fairly advanced
stuff.

With Conditional formatting (Formatconditional formatting), you can
set the format according to what is in the cell OR what is in another
cell in the same sheet.

So if you want the the drop down box to be Red if there is 'N0' in it
Condition1 Cell ValueIs equal to="No"

Then select format and you gett he chance to select the coulour of the
font, cell infill and border.

Then select 'ADD' and you get the chance to set another format - e.g.
for Yes. Repeat the process.

You can use al the normal Excel rules like IF, AND etc to build up
complicated setups.

Hope this helps

Ed


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