Thread: 2 Conditional Formats using VBA

I have written a piece of VBA code for a worksheet and I'm having an issue with performance. The purpose of the VBA code is to look for the word "Flag" and change the entire row to blue, and the word "Flag2" and change the font to light gray.

I also have another macro that I recorded that just inserts a new row and copies the formulas from the row above it and copies the formatting from the row below it.

before this code:

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyFlag = Range("P5:P960")
    For Each cell In MyFlag
        If cell.Value = "flag" Then
            cell.EntireRow.Interior.ColorIndex = 33
            cell.EntireRow.Font.ColorIndex = 1
        End If
        If cell.Value = "flag2" Then
            cell.EntireRow.Interior.ColorIndex = x1none
            cell.EntireRow.Font.ColorIndex = 15
        End If
        If cell.Value = "" Then
            cell.EntireRow.Interior.ColorIndex = x1none
        End If
    Next
End Sub

The worksheet was fine, but after it takes forever to run the macro to create a new line. I'm guessing it is that way since it has to recheck every line again to see if the code I posted applies to every line again.

Is there anyway to make this code more efficient?

Thanks,

Hyuk

Good idea. I will make sure it's okay with the workbook owner since it will be hard to create 800 rows of sample data.

Originally Posted by TMShucks

Welcome to the forum.

Please take a few moments to read the forum rules and then amend your thread title. Perhaps "worksheet change event poor performance".

Edit I suggest you post a sample workbook so all the code and the issue can be seen in context.

Regards