Conditional Formatting - Input Time Delays

[Artimues]

Product - Excel 2007

I have an issue in the time frame it takes to type in information into my
worksheet after creating multiple conditional formats for cells in it. My
intent is to make formatting of some cells turn gray if a previous column
cell has a specific data value.

The problem comes in to effect because I have over 5000+ conditional formats
now in the workbook. This is causing A LOT (5+ seconds) of lag input delay
time every time a cell is changed. (I used visual basic to enter in the
respective variables as shown below) I am *hoping* that instead of creating
EACH cell to have a conditional format for ONE CELL at a time, I could have a
column of cells that would change based upon the respective row value.

This is the macro I created to create the conditional formatting - I am
hoping that in lieu of having many conditional formats, by having 1
conditional format in a formula reference, this will cause my system to have
less delay @ the input stage.

For A = 0 To 1499
Range("N7").Select
ActiveCell.Offset(A, 0).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" &
ActiveCell.Offset(0, -2).Address & "=""NF"""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Next

 

[Sheeloo]

You are right...
Try
Sub Macro1()
'For A = 0 To 1499
' Range("N7").Select
Range("N7:N1499").Select
' ActiveCell.Offset(A, 0).Select
' Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" &
' ActiveCell.Offset(0, -2).Address & "=""NF"""
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=L7=""NF"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
'Next
End Sub

 

[Artimues]

The macro you posted Sheeloo will make all the cells N7:1499 grey out if the
value of L7="NL" which is not exactly what I require.

I need a formula that will reference the column I need it to. For the
example I gave I would need N7 to grey out if L7 value was "NL" - I need the
pattern to continue; ie. N8 to grey out if L8 value was "NL". My macro I
posted does this, but does it per cell - I am trying to get a FORMULA to
input into the conditional formatting tool that will fit all that conditional
formatting into one conditional format that would apply to those cells.

For example - the macro I gave makes 1500 conditional formats in the
worksheet (1 for each cell)
If I took a column and conditional format it would only "show" as 1
conditional format on the worksheet; it would just apply to each cell
individually.

I am hoping by "reducing" the number of 1500 conditional formats to 1 that
references cells individually it will reduce the input lag I am experiencing.
I hope this clarification helps.

 

[T. Valko]

Have you tried using the ribbon commands to apply the cf?