Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting does not change when values change. ?? | Excel Worksheet Functions | |||
mental block | Excel Worksheet Functions | |||
Mental block | New Users to Excel | |||
block event | Excel Programming | |||
Event for conditional formatting? | Excel Programming |