Conditional Formatting - Input Time Delays
 

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Next


--
The only mistake you will ever make is one you learn nothing from

Conditional Formatting - Input Time Delays
 

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
'Next
End Sub
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Artimues" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Next


--
The only mistake you will ever make is one you learn nothing from

Conditional Formatting - Input Time Delays
 

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.

--
The only mistake you will ever make is one you learn nothing from


"Sheeloo" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
'Next
End Sub
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Artimues" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Next


--
The only mistake you will ever make is one you learn nothing from

Conditional Formatting - Input Time Delays
 

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

--
Biff
Microsoft Excel MVP


"Artimues" wrote in message
...
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.

--
The only mistake you will ever make is one you learn nothing from


"Sheeloo" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
'Next
End Sub
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Artimues" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Next


--
The only mistake you will ever make is one you learn nothing from

Conditional Formatting - Input Time Delays
 

No, it won't. Pl. try it out... don't reject without trying... I posted only
after testing it

Excel adjusts 7 in L7 (due to relative reference) to the row it is applied
to...

that is why I removed the $...

btw code checks for NF and not NL...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Artimues" wrote:

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.

--
The only mistake you will ever make is one you learn nothing from


"Sheeloo" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
'Next
End Sub
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Artimues" wrote:

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.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Next


--
The only mistake you will ever make is one you learn nothing from