Hi, can I replace plain text with bold text using Find/Replace or possibly with a macro?

[Derek]

Hi,

Can I replace some text with bold text, and possibly add background colour
too?

I have a calendar of events where some are more important than others. For
example the text says Birthday then I want that Bold, if it says something
else then leave it as it is. So I could search for text that includes
"birthday" and modify all the text in that cell to Bold.

Also, is it possible to change the background colour for the cells in the
first coloumn (date) and alternate between 2 colours, so that each week is
highlighted by an alternating colour? Currently pale blue and pale green.


Example:

Sat 06 Mar 2004 Corporate Events
Sun 07 Mar 2004 Corporate Events
Sun 07 Mar 2004 Private Function
Mon 08 Mar 2004 Public Event
Tue 09 Mar 2004 Public Event
Wed 10 Mar 2004 Public Event
Thu 11 Mar 2004 Public Event


I'd like to be able to use something like Find/Replace and enter in Private
Function (plain) and replace with Private Function (bold).
Also, to have the date column's background colour alternate between pale
blue and pale green (using Fill color), just that column.

Thank you.

 

[Philip Reece-Heal]

Derek

In the Edit menu, click on Replace to open the find and replace box. In that
box, click on Options. You will see that not only can you find text/numbers
and replace them, you can also click on the format button to select various
formats.

Hope that helps

Philip

 

[Max]

... Also, is it possible to change the background colour for the cells in the
first coloumn (date) and alternate between 2 colours, so that each week is
highlighted by an alternating colour? Currently pale blue and pale green.

Try Conditional Formatting?

Select the column

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Formula Is | =mod(row(),2)

Click Format button > Patterns tab > Pale Green? > OK

Click OK at the main dialog

I'd stop here as the alternating green and "white" for the column looks
pleasant enough?
But if you really want the fill colour to be pale blue, just select the
column and then
select the fill colour to be pale blue via the fill icon in the formatting
toolbar

 

[Derek]

Philip,

I don't see that option, maybe it's not in my version of Excel. I should
have said it's Excel 2000, 9.0.6926 SP3. However I do get an option in Word
2000 that lets me click on "More" and then I can find/replace all sorts of
different fomatting. If it's in Word2000 will it be in Excel2000?

Thanks.

 

[Derek]

in green.

Try Conditional Formatting?

Select the column

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Formula Is | =mod(row(),2)

Click Format button > Patterns tab > Pale Green? > OK

Click OK at the main dialog

I'd stop here as the alternating green and "white" for the column looks
pleasant enough?

The alternating colours look really good, and it would be great for when I
have calendars that have different day on each row so thanks, I'm keeping
this tip!

But this calendar is "event's per row", not days per row. This means I can
have the same date 5 times in a row as I've got 5 events that day, the row
lists the info for the event on that day. Sometimes I'll have days where
there are no events so these dates aren't on this calendar. Imagine having
5 function rooms in a hotel, some days you'll have all the rooms busy with
different events, some days you'll have nothing on. We want to change the
colour by week, so we can easily see which are the busy weeks, and which
events during these weeks are most important.

So week1 would be blue, week2 green, week3 blue, week4 green, etc. What we
end up with is weeks that are short because they have less events, and weeks
that are very long because they have many events. And we want to be able to
see some events above others so we'd like to be able to modify the fonts
into bold, or some other easily identifiable format such as a different
colour font.

Can this be done in Excel 2000?

 

[Max]

... The alternating colours look really good, and it would be great for when I
have calendars that have different day on each row so thanks, I'm keeping
this tip!

But this calendar is "event's per row", not days per row. This means I can
have the same date 5 times in a row as I've got 5 events that day, the row
lists the info for the event on that day. Sometimes I'll have days where
there are no events so these dates aren't on this calendar. Imagine having
5 function rooms in a hotel, some days you'll have all the rooms busy with
different events, some days you'll have nothing on. We want to change the
colour by week, so we can easily see which are the busy weeks, and which
events during these weeks are most important.

So week1 would be blue, week2 green, week3 blue, week4 green, etc. What we
end up with is weeks that are short because they have less events, and weeks
that are very long because they have many events. And we want to be able to
see some events above others so we'd like to be able to modify the fonts
into bold, or some other easily identifiable format such as a different
colour font.

Taking your sample data set in the original post:

Sat 06 Mar 2004 Corporate Events
Sun 07 Mar 2004 Corporate Events
Sun 07 Mar 2004 Private Function
Mon 08 Mar 2004 Public Event
Tue 09 Mar 2004 Public Event
Wed 10 Mar 2004 Public Event
Thu 11 Mar 2004 Public Event

Assuming col A = Day-of-week (Sat, Sun, Mon, etc), col B = date &
col C is where you have text: Corporate Events, Private Function & Public
Event

a) you can apply Conditional Formatting (CF) up to a max of 3 conditions
(i.e. 3 conditions other than the default "normal" non-CF state)
to highlight cells in col C based on the text entered

Select col C

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Cell Value Is | equal to | ="Corporate Events"
Format to taste, e.g.: Format > Font tab > Bold? > OK

Click Add >> (this adds the 2nd condition)

Under Condition 2, make the settings:
Cell Value Is | equal to | ="Private Function"
Format to taste, e.g.: Format > Font tab > Bold & red ? > OK

Click Add >> (this adds the 3rd condition)

Under Condition 3, make the settings:
Cell Value Is | equal to | ="Public Event"
Format to taste, e.g.: Format > Font tab > Bold & dark blue & italics ? > OK

Click OK at the main dialog

With the settings above, you'll get the text/cell highlights you want to
differentiate
[Note that you could also apply the fill colour CF concurrently for each of
the 3 conditions
via the Patterns tab as part of the CF together with the formats made under
the Font tab]

If you have more than 3 conditions to format, you'd probably need a macro

Here's the links to 2 recent posts by Dave Peterson & Bob Phillips
which illustrates how
a. http://tinyurl.com/3cex5 [by Dave Peterson]
b. http://tinyurl.com/2apja [by Bob Phillips]

Check out also JE McGimpsey's page at:
http://www.mcgimpsey.com/excel/conditional6.html

which also contains links to Dave McRitchie's & Chip Pearson's CF page

---------------------------

b) Now for the trickier col B ..

In an empty col, say col D,

Put in D2: =WEEKNUM(B2,2)
Put in E2: =IF(ISBLANK(D2),"",IF(ISODD(D2),"O",IF(ISEVEN(D2),"E")))

Select D2:E2 and copy down as many rows as there is data in col B

[Note: WEEKNUM(), ISODD() and ISEVEN()
requires the Analysis Toolpak add-in to be installed and enabled]

The number returned in col D is essentially the week# of the year
(assuming week begins on Monday)

For the purposes of conditionally formatting col B
with the desired 2 alternating fill colours of pale green / pale blue
to differentiate between weeks in the year,
we'll try to use this week#s' "property" captured in col E
of being either odd or even

Select col *B*

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Formula Is | =E1="E"
Click Format button > Patterns tab > Pale Green > OK

Click Add >>

Under Condition 2, make the settings:
Formula Is | =E1="O"
Click Format button > Patterns tab > Pale Blue > OK

Click OK at the main dialog

The above CF should return the desired
alternating pale green / pale blue fills in col B
for alternating even / odd week#s,
assuming that there is at least 1 event/row entry per week in the year
(which should "normally" be the case? <g>)

Hide cols D & E, if desired

 

[Max]

Typo correction:

Line

In an empty col, say col D,

should read as

 

[Max]

Just to add-on a little more:

If your "date" data [sample below] is all in 1 col, say col A

Sat 06 Mar 2004
Sun 07 Mar 2004
Sun 07 Mar 2004
Mon 08 Mar 2004

here's a quick way to parse the day-of-week from the actual date
so that the earlier formulas suggested will work

Insert a new col B (this is for us to throw the actual dates into)

Select col A

Click Data > Text to Columns

Check "Fixed width" > Next

In step 2 of the wizard:

 

[Derek]

Thank you Max, I'll be trying out everything you wrote later on.

Regards,
Derek.

 

[Max]

You're welcome, Derek !
Drop a line or 2 here on how it turned out