Selecting an unusual range

[dailem]

I'm trying to start at a given cell (let's say B4). I want to
highlight all the columns that have data then subtract -1. I was
playing around with Selection.End(xlEnd) - 1, but that doesn't seem to
work. I then want to use that selection to highlight downward all of
the rows that have active information but again subtract -1 from that
range. Basically what I'm doing is eliminating the totals of my rows &
columns to be part of the conditional formatting that I want to run.
Any help w/ this would be appreciated!!!

 

[Guest]

To me, your description is a little sketchy, but try this on a copy of your
data.
Sub subtract1FromNumbers()
Dim rng as Range, cell as Range
set rng = Range("B4").currentRegion
for each cell in rng.Specialcells(xlConstants,xlNumbers)
if cell.column >=2 and cell.row >=4 then
cell = cell - 1
end if
Next
End sub

 

[Bernie Deitrick]

Try this:

Sub AddCF()
Dim myR As Range
Set myR = Range(Range("B4").End(xlToRight).Offset(0, -1), _
Range("B4").End(xlDown).Offset(-1, 0))
myR.FormatConditions.Delete

myR.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="24"
myR.FormatConditions(1).Interior.ColorIndex = 37
End Sub

Of course, the cond. formatting would need to be changed - record a macro doing the CF that you
want, then modify the above macro to use teh recorded CFing...

HTH,
Bernie
MS Excel MVP