-
vba cell format strikthrough and red
I understand how to use normal conditional formatting for a cell formating from the conditional; format selection. But I've used all 3 available formats for excel 2003.
I want the VBA code for if cell AJ301 < 10 then
range("c306:x1000) text goes red and strikethrough
Any help is appreciated. I havent used vba cell conditional formatting. Thanks.
-
Right click the sheet tab, view code, and paste:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Range("AJ301"))
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("C306:X1000")
If Range("AJ301").Value2 < 10 Then
.Font.Strikethrough = True
.Font.Color = vbRed
Else
.Font.Strikethrough = False
.Font.Color = vbBlack
End If
End With
Application.EnableEvents = True
End Sub[/VBA]
If the Target cell value is based on a formula, another approach is needed.
-
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("C306:X1000")
With r.Font
If Range("AJ301") < 10 Then
.Strikethrough = True
.Color = 255
Else
.Strikethrough = False
.ColorIndex = xlAutomatic
End If
End With
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules