Mental Block! - Event Change Conditional Formatting
 

Please put me out my misery folkes.
I have a range of data of which I want to conditional format the
numbers in columns G to L inclusive (yes the columns also contain
text).
I want to do something along the lines of :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = Columns("G:L")
If Target.Value < 1 Then
Selection.Font.ColorIndex = 3
End If
End Sub

I know this syntax is wrong but hopefully it shows you what I'm after.I
know I will have to add an IsNumber in there also, but my main question
is how you reference the Target to what you want it to be.

thanks folkes.
Donna

Mental Block! - Event Change Conditional Formatting
 

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = Columns("G:L")
If Target.Value < 1 Then
*Target*.Font.ColorIndex = 3
End If
End Sub


It would however be handier to use the Conditional Format tool. Select
Columns G:L and in the "Formula Is" appropriate box, type in =G1<1;
Choose format option to suit.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=373448

Mental Block! - Event Change Conditional Formatting
 

Hi,
try

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
Application.EnableEvents = False
If Not Intersect(Target, Range("G:L")) Is Nothing Then
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
If .Value < 1 Then
.Font.ColorIndex = 3
End If
End If
End With
End If
ExitSub:
Application.EnableEvents = True
End Sub


HTH

" wrote:

Please put me out my misery folkes.
I have a range of data of which I want to conditional format the
numbers in columns G to L inclusive (yes the columns also contain
text).
I want to do something along the lines of :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = Columns("G:L")
If Target.Value < 1 Then
Selection.Font.ColorIndex = 3
End If
End Sub

I know this syntax is wrong but hopefully it shows you what I'm after.I
know I will have to add an IsNumber in there also, but my main question
is how you reference the Target to what you want it to be.

thanks folkes.
Donna

Mental Block! - Event Change Conditional Formatting
 

Thanks for your repluys Chaps.
David, I can't use the conditional Format tool (I don't think) as I
have in effect 2 contitional formats to apply to each cell, but as far
as I can gather one always has priority over the other so both can not
be applied at the same time.

I got my selectionchange event to work but found it quite slow in
updating the cells every time you selected a cell let alone changing it
so I have set it to run with a button as essentially it only needs
updating when more data is imported into the file.

Thanks again for your time.
Donna