MrExcel.com - Your One Stop for Excel Tips & Solutions

conditional format(dave I need your help again)

Posted by steve on February 12, 2001 7:33 PM

hi dave'

with the code that you gave me I have it working with letters fine. The problem is when I try to reference it to another cell.(example =H1)

I don't think that it sees the outcome of the formula as the value, I think it sees the formula as it.

How do I fix this to work with referencing it to other cells.

I referenced out the second line to see if that would work , but it didn't.


thanks steve

Posted by Dave Hawley on February 12, 2001 10:08 PM


Hi steve

I can't really remember the macro, I think it had something to do with changing a cell colour if was between a certain value? if so and the value is in another cell(s) then try this:

Select Case Target
Case Range("H1") To Range("H2")
<Cell colour>

Dave
OzGrid Business Applications

Posted by STEVE on February 12, 2001 11:14 PM

HERES MY CODE, WHAT I'M TRYING TO DO IS TO BE ABLE TO HAVE A LINK TO A CELL OUTSIDE OF THE RANGE. (EXAMPLE IN CELL A1 I WANT TO HAVE THE FORMULA =H1.) IT COPYS THE CONTANTS OF H1 TO CELL A1 OVER FINE , BUT CONDITIONAL FORMATING ISN'T WORKING WHEN THE CELL IS A REFERENCE TO ANOTHER CELL.
THANKS STEVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:D10")) Is Nothing Then
Select Case Target
Case "T"
Target.Font.ColorIndex = 6
Case "C"
Target.Font.ColorIndex = 46
Case "H.W."
Target.Font.ColorIndex = 5
Case "V"
Target.Font.ColorIndex = 15
Case "S.V."
Target.Font.ColorIndex = 50
End Select
End If
End Sub

Posted by Dave Hawley on February 13, 2001 10:52 PM


Hi Steve

This is because the the change Event is NOT fired by a cell formula result changing. To trap this you will need to use the Calculate Event. Just copy the below and paste it above the other code.


Private Sub Worksheet_Calculate()
Dim MyCell As Range
For Each MyCell In Range("A1:D10").SpecialCells(xlCellTypeFormulas)
Select Case MyCell
Case "T"
MyCell.Font.ColorIndex = 6
Case "C"
MyCell.Font.ColorIndex = 46
Case "H.W."
Target.Font.ColorIndex = 5
Case "V"
MyCell.Font.ColorIndex = 15
Case "S.V."
MyCell.Font.ColorIndex = 50
End Select
Next MyCell
End Sub


Good luck

Dave


OzGrid Business Applications

Posted by Dave Hawley on February 13, 2001 10:55 PM

Oops, missed a bit!


Relace: Target.Font.ColorIndex = 5
With
MyCell.Font.ColorIndex = 5


OzGrid Business Applications