Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting cells which do not meet a formatting criteria, and assigning a Range object to these.
Hi
My worksheet contains some red (i.e. Cells(x,y).Font.Color = vbRed) cells which I don't my macro to touch when it runs. My question is: Is there a way to determine which cells aren't red and assign a range object to these cells (say 'Non_Red_Cells' as Range)? Below is a description of how I've thought of approaching this (in 2 steps), but need help with the code. Please feel free to suggest a more direct/easier method * Could you please help me out with a piece of code which will assign a Range object to the cells that are red? That is, conceptually, I'd like it to work like the Specialcells method, but forthe cells whose font is red i.e --------------------------------------------- Dim Red_Cells as Rang Set Red_Cells = {need help with code --------------------------------------------- * After determining Red_Cells, I would then like to set a range object 'Non_Red_Cells' to the non-red range of my UsedRange. Conceptually I guess we can think of it as the Activesheet.Usedrange minus the Red_Cells. It is on this range that I would like my macro to operate on. Is it possible to get this Thanks very much for your help in advance! = SuperJas. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting cells which do not meet a formatting criteria, andassigning a Range object to these.
You could loop through the cells:
Option Explicit Sub testme() Dim RngRed As Range Dim Rng As Range Dim myCell As Range Set Rng = Selection 'activesheet.usedrange For Each myCell In Rng.Cells If myCell.Font.Color = vbRed Then If RngRed Is Nothing Then Set RngRed = myCell Else Set RngRed = Union(myCell, RngRed) End If End If Next myCell If RngRed Is Nothing Then MsgBox "no red" Else RngRed.Select '???? End If End Sub SuperJas wrote: Hi, My worksheet contains some red (i.e. Cells(x,y).Font.Color = vbRed) cells which I don't my macro to touch when it runs. My question is: Is there a way to determine which cells aren't red and assign a range object to these cells (say 'Non_Red_Cells' as Range)? Below is a description of how I've thought of approaching this (in 2 steps), but need help with the code. Please feel free to suggest a more direct/easier method: * Could you please help me out with a piece of code which will assign a Range object to the cells that are red? That is, conceptually, I'd like it to work like the Specialcells method, but forthe cells whose font is red. i.e. ---------------------------------------------- Dim Red_Cells as Range Set Red_Cells = {need help with code} ---------------------------------------------- * After determining Red_Cells, I would then like to set a range object 'Non_Red_Cells' to the non-red range of my UsedRange. Conceptually I guess we can think of it as the Activesheet.Usedrange minus the Red_Cells. It is on this range that I would like my macro to operate on. Is it possible to get this? Thanks very much for your help in advance! =) SuperJas. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting cells which do not meet a formatting criteria, and assigning a Range object to these.
SuperJas
Try this Sub NonRedCells() Dim NonRed As Range Dim cell As Range For Each cell In Sheet1.UsedRange.Cells If cell.Font.Color < vbRed Then If NonRed Is Nothing Then Set NonRed = cell Else Set NonRed = Union(NonRed, cell) End If End If Next cell MsgBox NonRed.Address End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "SuperJas" wrote in message ... Hi, My worksheet contains some red (i.e. Cells(x,y).Font.Color = vbRed) cells which I don't my macro to touch when it runs. My question is: Is there a way to determine which cells aren't red and assign a range object to these cells (say 'Non_Red_Cells' as Range)? Below is a description of how I've thought of approaching this (in 2 steps), but need help with the code. Please feel free to suggest a more direct/easier method: * Could you please help me out with a piece of code which will assign a Range object to the cells that are red? That is, conceptually, I'd like it to work like the Specialcells method, but forthe cells whose font is red. i.e. ---------------------------------------------- Dim Red_Cells as Range Set Red_Cells = {need help with code} ---------------------------------------------- * After determining Red_Cells, I would then like to set a range object 'Non_Red_Cells' to the non-red range of my UsedRange. Conceptually I guess we can think of it as the Activesheet.Usedrange minus the Red_Cells. It is on this range that I would like my macro to operate on. Is it possible to get this? Thanks very much for your help in advance! =) SuperJas. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting cells which do not meet a formatting criteria, and assigning a Range object to these.
This assumes the font color is not produced by conditional formatting.
Dim red_cells as Range Dim non_red_cells as Range Dim cell as Range for each cell in Activesheet.UsedRange if cell.Font.ColorIndex = 3 then if redcells is nothing then set red_cells = cell else set red_cells = Union(red_cells,cell) end if else if nonredcells is nothing then set non_red_cells = cell else set non_red_cells = Union(non_red_cells,cell) end if end if Next -- Regards, Tom Ogilvy "SuperJas" wrote in message ... Hi, My worksheet contains some red (i.e. Cells(x,y).Font.Color = vbRed) cells which I don't my macro to touch when it runs. My question is: Is there a way to determine which cells aren't red and assign a range object to these cells (say 'Non_Red_Cells' as Range)? Below is a description of how I've thought of approaching this (in 2 steps), but need help with the code. Please feel free to suggest a more direct/easier method: * Could you please help me out with a piece of code which will assign a Range object to the cells that are red? That is, conceptually, I'd like it to work like the Specialcells method, but forthe cells whose font is red. i.e. ---------------------------------------------- Dim Red_Cells as Range Set Red_Cells = {need help with code} ---------------------------------------------- * After determining Red_Cells, I would then like to set a range object 'Non_Red_Cells' to the non-red range of my UsedRange. Conceptually I guess we can think of it as the Activesheet.Usedrange minus the Red_Cells. It is on this range that I would like my macro to operate on. Is it possible to get this? Thanks very much for your help in advance! =) SuperJas. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting cells which do not meet a formatting criteria, and assigning a Range object to these.
One way:
Dim Red_Range As Range Dim Non_Red_Range As Range Dim rCell As Range For Each rCell In ActiveSheet.UsedRange If rCell.Font.ColorIndex = 3 Then If Red_Range Is Nothing Then Set Red_Range = rCell Else Set Red_Range = Union(Red_Range, rCell) End If Else If Non_Red_Range Is Nothing Then Set Non_Red_Range = rCell Else Set Non_Red_Range = Union(Non_Red_Range, rCell) End If End If Next rCell If Not Red_Range Is Nothing Then _ MsgBox "red range: " & Red_Range.Address(0, 0) If Not Non_Red_Range Is Nothing Then _ MsgBox "non-red range: " & Non_Red_Range.Address(0, 0) In article , "SuperJas" wrote: Hi, My worksheet contains some red (i.e. Cells(x,y).Font.Color = vbRed) cells which I don't my macro to touch when it runs. My question is: Is there a way to determine which cells aren't red and assign a range object to these cells (say 'Non_Red_Cells' as Range)? Below is a description of how I've thought of approaching this (in 2 steps), but need help with the code. Please feel free to suggest a more direct/easier method: * Could you please help me out with a piece of code which will assign a Range object to the cells that are red? That is, conceptually, I'd like it to work like the Specialcells method, but forthe cells whose font is red. i.e. ---------------------------------------------- Dim Red_Cells as Range Set Red_Cells = {need help with code} ---------------------------------------------- * After determining Red_Cells, I would then like to set a range object 'Non_Red_Cells' to the non-red range of my UsedRange. Conceptually I guess we can think of it as the Activesheet.Usedrange minus the Red_Cells. It is on this range that I would like my macro to operate on. Is it possible to get this? Thanks very much for your help in advance! =) SuperJas. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting cells which do not meet a formatting criteria, and assigning a Range object to these.
Thanks Dave! Thanks Dick! Thanks Tom! Thanks JE
All these work fantastic! =) Very happy now! =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Rows that meet criteria | Excel Worksheet Functions | |||
Count Cells that meet Criteria | Excel Worksheet Functions | |||
Sum Total # Of Cells That Meet Several Criteria | Excel Worksheet Functions | |||
Sum range of values that meet date criteria | Excel Discussion (Misc queries) | |||
Print cells that meet conditional formatting criteria | Excel Discussion (Misc queries) |