Deleting conditional formats based on condition
 

I have values in Range A5:A100 and conditional formats in
cells in range B5:B100. I need a code that will look at
the last value in range A5:A100 and then delete all
conditional formats ColumnB after the last value in Range
A5:A100.

For example if the last value in Range A5:A100 is in cell
A15, then I need for all conditional formats to be removed
from B16 and after.

Another example...
if the last value in Range A5:A100 is in cell A7, then I
need for all conditional formats to be removed from B8 and
after.

Thanx

todd Htutenstine

Deleting conditional formats based on condition
 

With Worksheets("Sheet3")
set rng = .Range(.Cells(.rows.count,1).end(xlup)(2), .Range("A100"))
End With
rng.offset(0,1).FormatConditions.Delete

if they are being put in by your previous code, then just prevent them with

Dim cellr As Range, res As Variant, res1 as Variant
Worksheets(3).Activate
For Each cellr In Worksheets(3).Range("A5:A100")
if isempty(cellr) then exit for ' <===
res = Application.VLookup(cellr.Value, _
Worksheets(4).Range("M2:Q100"), 4, False)
res1 = Application.VLookup(cellr.Value, _
Worksheets(4).Range("M2:Q100"), 5, False)

If Not IsError(res) Then
if instr(cellr.offset(0,1).NumberFormat,"%") then
res = res * 100
res1 = res1 * 100
End if
cellr.Offset(0, 1).Select
cellr.Offset(0, 1).FormatConditions.Delete
cellr.Offset(0, 1).FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotBetween, _
Formula1:=res, Formula2:=res1
cellr.Offset(0, 1).FormatConditions(1). _
Interior.ColorIndex = 3
End If
Next


--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
I have values in Range A5:A100 and conditional formats in
cells in range B5:B100. I need a code that will look at
the last value in range A5:A100 and then delete all
conditional formats ColumnB after the last value in Range
A5:A100.

For example if the last value in Range A5:A100 is in cell
A15, then I need for all conditional formats to be removed
from B16 and after.

Another example...
if the last value in Range A5:A100 is in cell A7, then I
need for all conditional formats to be removed from B8 and
after.

Thanx

todd Htutenstine

Deleting conditional formats based on condition
 

Todd,

Try this

Dim cLastRow As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B" & cLastRow + 1 & ":B" & Rows.Count).FormatConditions.Delete


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
I have values in Range A5:A100 and conditional formats in
cells in range B5:B100. I need a code that will look at
the last value in range A5:A100 and then delete all
conditional formats ColumnB after the last value in Range
A5:A100.

For example if the last value in Range A5:A100 is in cell
A15, then I need for all conditional formats to be removed
from B16 and after.

Another example...
if the last value in Range A5:A100 is in cell A7, then I
need for all conditional formats to be removed from B8 and
after.

Thanx

todd Htutenstine

Deleting conditional formats based on condition
 

Thanx


-----Original Message-----
I have values in Range A5:A100 and conditional formats in
cells in range B5:B100. I need a code that will look at
the last value in range A5:A100 and then delete all
conditional formats ColumnB after the last value in Range
A5:A100.

For example if the last value in Range A5:A100 is in cell
A15, then I need for all conditional formats to be
removed
from B16 and after.

Another example...
if the last value in Range A5:A100 is in cell A7, then I
need for all conditional formats to be removed from B8
and
after.

Thanx

todd Htutenstine

.