Is there a way to format any row in the workbook based on text value in VBA?
e.g. If I type "BLANK" in any row of a worksheet, then the certain cells in that particular row will Format Borders xlDiagonalUp (D:G,K).
[Blocked Image: http://i51.photobucket.com/albums/f375/trillskill/XL_blank.gif]
Re: Format Row Based On Text Value
This is code that goes in the worksheet code module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
If Target.Text = "Blank" Then
Range("D" & Target.Row & ":G" & Target.Row & ",K" & Target.Row).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
With Selection.Borders(xlDiagonalUp)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End If
End SubRe: Format Row Based On Text Value
Simon [COLOR="Red"]Thank You[/COLOR] worked perfect!
Just had to remove the Tint and Shade property.
Re: Format Row Based On Text Value
Simon
That code could be trimmed a little.:)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
If Target.Text = "Blank" Then
Set rng = Range("D" & Target.Row & ":G" & Target.Row & ",K" & Target.Row)
With rng.Borders(xlDiagonalUp)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.Weight = xlThin
End With
End If
End SubRe: Format Row Based On Text Value
[COLOR="Red"]THANK YOU[/COLOR] The Code worked perfectly!
I just needed to remove the tint and shade property.
Thank you again.
ANB
Re: Format Row Based On Text Value
Why not use Conditional Formatting?
Don’t have an account yet? Register yourself now and be a part of our community!