macro to format row based on cell value

[balaji3081]

Hi All,

I am trying to get a two rows formatted based on the value in the range, my code has a event worksheet_change which is disabling my excel undo function, I am not at all comfortable with this, is there a better code to give me the result -


Below is the code I am currently using -

Sub Worksheet_Change(ByVal Target As Range)

Range("h40:aa63").Font.Bold = False
Range("h40:aa63").Font.Underline = False

Dim row1 As Integer
Set MyPlage = Range("h40:h63")

For Each cell In MyPlage

Select Case cell.Value

Case Is = "Core"

cell.Font.Bold = True
row1 = cell.Row - 1
Sheets("LIE").Range("i" & row1 & ":aa" & row1).Font.Underline = True

Case Is = "Customer Assistance"

cell.Font.Bold = True
row1 = cell.Row - 1
Sheets("LIE").Range("i" & row1 & ":aa" & row1).Font.Underline = True

Case Is = "Default  "

cell.Font.Bold = True
row1 = cell.Row - 1
Sheets("LIE").Range("i" & row1 & ":aa" & row1).Font.Underline = True

Case Is = "Support"

cell.Font.Bold = True
row1 = cell.Row - 1
Sheets("LIE").Range("i" & row1 & ":aa" & row1).Font.Underline = True

Case Is = "Mortgage Servicing"

cell.Font.Bold = True
cell.Interior.ColorIndex = 6
row1 = cell.Row - 1
Sheets("LIE").Range("i" & row1 & ":aa" & row1).Font.Underline = True

Case Is = "Other"

cell.Font.Bold = True
row1 = cell.Row - 1
Sheets("LIE").Range("i" & row1 & ":aa" & row1).Font.Underline = True



'Case Is = "Default"
'cell.Font.Bold = True
'row1 = cell.Row - 1
'Sheets("Sheet1").Range("C" & row1 & ": J" & row1).Font.Underline = True


'Case Is = "P"
'cell.EntireRow.Interior.ColorIndex = 6




End Select
Next
End Sub

 

[Chihiro]

Read through the article in the link. It uses 2 class modules that can plug into most project to work as undo handler.

http://www.jkp-ads.com/Articles/UndoWithVBA00.asp

Do note below from conclusion of the article (in your case, it should not have impact).

The actions the undo handler can undo are limited to changes to properties of objects in general. Things like inserting or deleting sheets, refreshing querytables, updating Pivottables and etcetera, cannot be undone using this technique.

 

[balaji3081]

Thanks for the reply, Will go through the link, Just to be clear 'undo' function I am referring to is the excel undo and not the macro to undo the macro.


Regards.

 

[Chihiro]

I understand. But by default, once the code runs, it will not allow Excel undo. That's why you need a workaround.

 

[balaji3081]

Great , we are on the same page, Thanks.

Just so to know is there a better way to write to code other than using 'Worksheet_change'.

Regards.

 

[Chihiro]

Normally I'd assign it to a button or just run it with short key combo.

I only use worksheet change event when I need to validate change made by user(s) or in Dashboard type of set up (tied to dropdown etc).