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
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