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?

 

[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)

 

[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]

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=

 

[Bob Phillips]

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 Format>Conditional 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)

 

[EdMac]

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

With Conditional formatting (Format>conditional formatting), you ca
set the format according to what is in the cell OR what is in anothe
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 Value>Is equal to>="No"

Then select format and you gett he chance to select the coulour of th
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 u
complicated setups.

Hope this helps

E