PDA

View Full Version : vba cell format strikthrough and red



wilg
01-11-2012, 04:39 PM
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.

Kenneth Hobs
01-11-2012, 05:36 PM
Right click the sheet tab, view code, and paste:
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

If the Target cell value is based on a formula, another approach is needed.

mdmackillop
01-11-2012, 05:49 PM
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