I have a sheet with a number of links to another worksheet. What i now want
to add is automatic colour change in cells dependant on input from another
formula eg:-

If cell B3 formula answer says E i want all cells below in the same column
to turn a different for example blue. There will be four possible options :-
E L M or O, and the fields needing filling will be from B6 - B31 to P6 - P31

Is there a macro as i have used contitional formating for another field?

Set the Security level to low/medium in (Tools|Macro|Security). From workbook
launch VBE using short-key Alt+F11. From the left treeview search for VBA
Project for the current workbook. Click on + to expand it . You will find
'This Workbook' within the VBA project tree...Double click and paste the
below code and try ..Dont forget to edit the sheetname..in which you need to
fill color.


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
With Sheets("Sheet3")
..Range("B6:P31").Interior.ColorIndex = -4142
Select Case .Range("B3")
Case "E"
..Range("B6:P31").Interior.ColorIndex = 5
Case "L"
..Range("B6:P31").Interior.ColorIndex = 4
Case "M"
..Range("B6:P31").Interior.ColorIndex = 3
Case "O"
..Range("B6:P31").Interior.ColorIndex = 6
End Select
End With
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Dan Wood" wrote:

I have a sheet with a number of links to another worksheet. What i now want
to add is automatic colour change in cells dependant on input from another
formula eg:-

If cell B3 formula answer says E i want all cells below in the same column
to turn a different for example blue. There will be four possible options :-
E L M or O, and the fields needing filling will be from B6 - B31 to P6 - P31

Is there a macro as i have used contitional formating for another field?

My first reflex would be to look into conditional formatting. What version of
Excel are you using?

Not everybody is familiar with the fact that many cells can be formatted
based on the input of the result of a formula. This result can even be
outside the range that will be formatted.

I am not sure to understand what you mean by "the fields needing filling
will be from B6 - B31 to P6 - P31".

If the value is E then B6:B31 becomes, say, blue. What happens between B and
P? Are other letters in other columns as well and is the colour is different?
Tell me if you are using XL 2007?

Cheers,

--
Daniel


"Dan Wood" wrote:

I have a sheet with a number of links to another worksheet. What i now want
to add is automatic colour change in cells dependant on input from another
formula eg:-

If cell B3 formula answer says E i want all cells below in the same column
to turn a different for example blue. There will be four possible options :-
E L M or O, and the fields needing filling will be from B6 - B31 to P6 - P31

Is there a macro as i have used contitional formating for another field?