PDA

View Full Version : Conditional formatting for entire row for text based values using VBA code



bhaktprahlad
08-31-2010, 07:03 AM
Hi,
I would like to highlight the entire row in an MS Excel2003 using conditional formatting based on a particular cell value. A similar thing has been solved here. ( I can't give the hyperlink here as I am a newbie but the code is given at the end of this email message. This is solved by DRJ. KB ID is 90)
All I require is a tweak that will highlight the entire row instead of just the cell which it is doing currently.
Also can anyone let me know a proper primer of VBA that helps a newbie understand VBA code. Ideally I should have been able to decode the VBA code and tweak it to my requirement, instead of wasting someone's precious time.
Thanking you in advance.
Warm Regards,
Kallol

Code:

Option Compare Text 'A=a, B=b, ... Z=zOption Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim Rng1 As Range On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False Case "Tom", "Joe", "Paul" Cell.Interior.ColorIndex = 3 Cell.Font.Bold = True Case "Smith", "Jones" Cell.Interior.ColorIndex = 4 Cell.Font.Bold = True Case 1, 3, 7, 9 Cell.Interior.ColorIndex = 5 Cell.Font.Bold = True Case 10 To 25 Cell.Interior.ColorIndex = 6 Cell.Font.Bold = True Case 26 To 99 Cell.Interior.ColorIndex = 7 Cell.Font.Bold = True Case Else Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False End Select Next End Sub

p45cal
08-31-2010, 11:03 AM
try instead of the likes of:
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True use:
Cell.Entirerow.Interior.ColorIndex = 3
Cell.Entirerow.Font.Bold = True

bhaktprahlad
08-31-2010, 09:33 PM
Hi p45cal,

That helped.

Thanks so much.

Warm Regards,
Kallol