Selecting an unusual range
 

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!!!

Selecting an unusual range
 

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

--
Regards,
Tom Ogilvy

"dailem" wrote:

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!!!

Selecting an unusual range
 

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


"dailem" wrote in message
ups.com...
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!!!