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
|