Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Help please in selecting range dependent on another range | Excel Programming | |||
Selecting a Range | Excel Programming | |||
Selecting a Range inside a range | Excel Programming | |||
Selecting a range | Excel Programming |