Help with If Not Intersect
alter it to this:
rivate Sub workbook_sheetchange(ByVal sh As Object, ByVal Target As Range)
Dim cell As Range
On Error Resume Next
For Each cell In Target
If Not Intersect(cell, Range("f5:f33")) Is Nothing Then
If cell.Value = 0.5 Then
cell.Font.ColorIndex = 3
cell.Font.FontStyle = "bold"
Else
cell.Font.ColorIndex = xlAutomatic
cell.Font.FontStyle = "normal"
End If
Else
cell.Font.ColorIndex = xlAutomatic
cell.Font.FontStyle = "normal"
End If
Next cell
End Sub
Regards,
Tom Ogilvy
"derek" wrote in message
...
Bob thanks for the quick response, but I'm still stuck,
I'm sure its me not explaing it right. You code works
fine but if I copy a cell from the range F5:F33 that has a
value of 0.6 (Its now red and bold because of your code)
that format is applied when pasted to say cell C5. I need
it to be black/normal. Users of my sheet are
occaisionally accidently copying and pasting a cell from
the F5:F33 range elsewhere in the sheet and what should be
black is formatted red.
TIA derek..
-----Original Message-----
Derek,
Try this, It's a bit more intensive, but works
Private Sub workbook_sheetchange(ByVal sh As Object,
ByVal Target As Range)
Dim cell As Range
On Error Resume Next
For Each cell In Target
If Not Intersect(cell, Range("f5:f33")) Is
Nothing Then
If cell.Value = 0.5 Then
cell.Font.ColorIndex = 3
cell.Font.FontStyle = "bold"
Else
cell.Font.ColorIndex = 1
cell.Font.FontStyle = "normal"
End If
End If
Next cell
End Sub
--
HTH
-------
Bob Phillips
... looking out across Poole Harbour to the Purbecks
"derek" wrote in message
...
Excel 2000
With a previous question and your help I'm up and
running
with the code below. But I need a last bit of help
please
to find away round a glitch..
Users enter data into the sheet within a range of
C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the
F5:F33
(from the code below) is transfered to another cell.
How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd
either
directly or copied from a cell with red/bold format?
Private Sub workbook_sheetchange(ByVal sh As Object,
ByVal
Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("f5:f33")) Is Nothing
Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle
= "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub
TIA Derek..
.
|