3+ conditional formats with wildcards

lrlake

New Member
Joined
Jan 27, 2005
Messages
5
I am trying to conditionally format a duty calendar that has each duty shown in a font of a specific color.

If the person is off duty the font remains black, but the cell is shaded gray if it says OFF and yellow if it says VAC or PFH.

Each cell contains the duty name and an ALT+ENTER to put the duty hours on a separate line. In conditional formatting, I was using this
Formula Is =NOT(ISERROR(SEARCH("ASE/Page 1*",B3))) and selecting a dull blue font color for the ASE/Page 1 duty. I did this for three of the duties and it worked fine, but I need more than three.

Here is a list of the duties and corresponding colors:
ASE/Page 1 ... dull blue
Page 2/rim ... green
Slot ... purple
Rim ... red
Early ... dark orange (Any cell that has early in it should be dark orange font)
Agate ... bright blue

Can anyone help?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
How about an event handler.
Press Alt-F11 to open VBA.
In the left pane double click the sheet you want this to work on.

Paste this in:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
test1 = ActiveCell.Text Like "*SE*"
If test1 = True Then Selection.Interior.ColorIndex = 55
test1 = ActiveCell.Text Like "*age 2*"
If test1 = True Then Selection.Interior.ColorIndex = 10
test1 = ActiveCell.Text Like "*lot*"
If test1 = True Then Selection.Interior.ColorIndex = 55
test1 = ActiveCell.Text Like "*im*"
If test1 = True Then Selection.Interior.ColorIndex = 3
test1 = ActiveCell.Text Like "*arly*"
If test1 = True Then Selection.Interior.ColorIndex = 46
test1 = ActiveCell.Text Like "*gate*"
If test1 = True Then Selection.Interior.ColorIndex = 5
End If
 

lrlake

New Member
Joined
Jan 27, 2005
Messages
5
OK, did all that, but it didn't seem to do anything to the text.

I admit to being absolutely clueless in VBA, so I may have missed a step that most people think is obvious.

Is the formatting supposed to work on any cell in the sheet automatically? Or am I supposed to select the cells and run something?
 

lrlake

New Member
Joined
Jan 27, 2005
Messages
5
Also, I need this to work in a workbook where the information is actually typed in and also in another workbook where the information is linked from the original.

In other words, I have a master file where I enter the information as text and each sheet in that file links to a separate workbook that displays the duty information, but not some other confidential information (vacation days remaining, etc.).
 

lrlake

New Member
Joined
Jan 27, 2005
Messages
5

ADVERTISEMENT

I have managed to get the coding right to get the fonts and background colors that i wanted, but how can i make the changes take effect immediately? Right now, after I enter the information, I have to click back on the cell to get the formatting to show up.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I assume that your cursor moves after selection. From the tools menu, choose options, then edit tab, then uncheck the "move selection after enter" check.

Alternatively, you can change:
ActiveCell.Text to activecell.offset(-1,0).Text
if you move down after enter.
 

lrlake

New Member
Joined
Jan 27, 2005
Messages
5
That takes care of that problem, thanks.

Now, can anyone tell me how to make the formatting take effect upon change through a link to another workbook? So if I make a change in the master worksheet, when a worksheet with cells linked to that master is opened, the cell contents AND formatting update. (Assuming the VBA code is the same for both worksheets).
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
You might then want to change it to:
Private Sub Worksheet_Calculate
 

Forum statistics

Threads
1,147,680
Messages
5,742,590
Members
423,740
Latest member
JCF_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top