Multiple range selection and editing

[Steven]

I have built a calendar in excel. Each day is made up of seven rows to put
todo monthly type items which is basically enough for one day. An example of
a item may be 'Fund Payroll - SG' and another item may be 'Co 1 F/S due - SG'
and these occur each month so each one will have at least 12 occurances. The
payroll will have 24 because there are 24 paydays in the year. My question
is how can I select at the same time all cells containing '- SG'. I want to
do this becuase as a user enters the system I want to color their
responsibility cells with color code 36, ie. the light yellow. But another
issue is I want to color their cells with code 36 only if the cells
Interior.ColorIndex <> xlnone. This is because I have all the
responsibilities with Interior.ColorIndex = 35 until the task is complete at
which time I make the Interior.ColorIndex = xlnone and therefore do not want
to Interior.ColorIndex the cell to 36 if the task is already completed and
the Interior.ColorIndex = xlnone.

Summary: All responsibility cells are colored with index = 35. When a user
enters I want to select all their cells identified with the, for example, ' -
SG' and test if the cell color index is not xlnone then I want to change it
to colorindex = 36 so their responsibility will be light yellow and all the
other responsibilites will show as light green.

This was a long explanation of the problem and I thank you very much for
your help.

Steven

 

[Norman Jones]

Hi Steven,

Try using Excel's Conditional Format tool.

If you are not familiar with Conditional
Formatting, see Debra Dalgleish's tutorial at:

Excel -- Conditional Formatting -- Introduction
http://www.contextures.com/xlCondFormat01.html

If you need to automate this, turn on the
macro recorder and perform the necessary
operations manually. This will provide you
with code which may be edited to afford
more generic application.

If you experience problems in editing the
recorder code, post back with the problematic
code.

 

[Steven]

Norman,

Thank you for the response. I kind of get it and then I do not. I am
thinking based on your suggestion is to create a range for all 12 months and
select that range and then I have to interior.colorindex all the cells
equaling ' - SG' to 36 as long as the cell is not currently
interior.colorindexed to xlnone. This is complex beyond by current
knowledge. Do you know how to accomplish this?

Thank you,

Steven

 

[Norman Jones]

Hi Steven,

The Conditional Format (CF) tool permits
the use three conditions. See the cited
Debra Dalgleish tutorial.

To respond to the specific problem your
raise, use the first CF condition to appply a
no-fill format to correspond to a completed
task indicator; then set the second condition
to corrrespond to your "- SG' to 36"
condition.

If you require more than 3 conditions, you
may wish to download xlDynamics' CFPlus
Add-in, which may be download at:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Steven]

Norman,

After thinking about it a while I remember I had a solution that I used for
a different issue that applies to this one.

For Each r In ActiveSheet.UsedRange
If Right(r.Value,4) = "- SG" And r.Interior.ColorIndex <> xlNone Then
r.InteriorColorIndex = 36
End If
Next

It can go through a large file in no time at all. Thank you for your input
on this subject.

Steven