I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Why use VB. You can use regular conditional formatting.
Change from Cell Value is to Formual is and use something like

=FIND("lunch", A1)0

If it finds the string lunch anywhre in the cell it returns true and formats
the cell. Off can be done in much the same way...

=FIND("off", A1)=0

HTH

"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

I can't really use Conditional Formatting because I will be exceeding the 3
formats allowed by Excel.
I tried the Add-in that was posted here by Frank Kabel some time ago, and it
is not fitting my needs either.
One thing I would like to avoid is having to have another user of the
workbook have to install an addin, or have access to a Macro in order to
view, or alter the formatting or contents of the workbook itself.

"Jim Thomlinson" wrote:

Why use VB. You can use regular conditional formatting.
Change from Cell Value is to Formual is and use something like

=FIND("lunch", A1)0

If it finds the string lunch anywhre in the cell it returns true and formats
the cell. Off can be done in much the same way...

=FIND("off", A1)=0

HTH

"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

So you want more than 3 conditions, you can't use CFPlus, you don't want to
use a macro (even though the subject says using VB ...).

To quote the proverb, you want your cake and eat it too. Can't be done.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fleone" wrote in message
...
I can't really use Conditional Formatting because I will be exceeding the
3
formats allowed by Excel.
I tried the Add-in that was posted here by Frank Kabel some time ago, and
it
is not fitting my needs either.
One thing I would like to avoid is having to have another user of the
workbook have to install an addin, or have access to a Macro in order to
view, or alter the formatting or contents of the workbook itself.

"Jim Thomlinson" wrote:

Why use VB. You can use regular conditional formatting.
Change from Cell Value is to Formual is and use something like

=FIND("lunch", A1)0

If it finds the string lunch anywhre in the cell it returns true and
formats
the cell. Off can be done in much the same way...

=FIND("off", A1)=0

HTH

"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like
it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I
have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Perhaps I am not using the correct terminology. I am making the assumption
that a Macro is not actually Visual Basic programming in Excel, but a
"recording", if you will, of repetitive tasks. If my use of the term VB is
incorrect, I apologize for the confusion.
I would merely like to have cells formatted based on their contents and
Conditional Formatting doesn't fulfill my needs in this instance due to the
limitation in the number of formatting instances that are allowed.
When attempting to use CF+ it would not apply my formatting request over the
range of cells that I specified. They were all concurrent cells (Range
B4:U14) and the CF+ add-in would apply the same formatting to the entire
range. I believe what was occuring is that the CF+ add-in was making a
reference to a single cell in the range, not the entire range.


"Bob Phillips" wrote:

So you want more than 3 conditions, you can't use CFPlus, you don't want to
use a macro (even though the subject says using VB ...).

To quote the proverb, you want your cake and eat it too. Can't be done.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fleone" wrote in message
...
I can't really use Conditional Formatting because I will be exceeding the
3
formats allowed by Excel.
I tried the Add-in that was posted here by Frank Kabel some time ago, and
it
is not fitting my needs either.
One thing I would like to avoid is having to have another user of the
workbook have to install an addin, or have access to a Macro in order to
view, or alter the formatting or contents of the workbook itself.

"Jim Thomlinson" wrote:

Why use VB. You can use regular conditional formatting.
Change from Cell Value is to Formual is and use something like

=FIND("lunch", A1)0

If it finds the string lunch anywhre in the cell it returns true and
formats
the cell. Off can be done in much the same way...

=FIND("off", A1)=0

HTH

"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like
it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I
have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Fleone,

I wrote CF+ with Frank. If you want to mail me direct, perhaps we could
continue the discussion offline, and hopefully solve your problem (one way
or another).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fleone" wrote in message
...
Perhaps I am not using the correct terminology. I am making the assumption
that a Macro is not actually Visual Basic programming in Excel, but a
"recording", if you will, of repetitive tasks. If my use of the term VB is
incorrect, I apologize for the confusion.
I would merely like to have cells formatted based on their contents and
Conditional Formatting doesn't fulfill my needs in this instance due to
the
limitation in the number of formatting instances that are allowed.
When attempting to use CF+ it would not apply my formatting request over
the
range of cells that I specified. They were all concurrent cells (Range
B4:U14) and the CF+ add-in would apply the same formatting to the entire
range. I believe what was occuring is that the CF+ add-in was making a
reference to a single cell in the range, not the entire range.


"Bob Phillips" wrote:

So you want more than 3 conditions, you can't use CFPlus, you don't want
to
use a macro (even though the subject says using VB ...).

To quote the proverb, you want your cake and eat it too. Can't be done.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fleone" wrote in message
...
I can't really use Conditional Formatting because I will be exceeding
the
3
formats allowed by Excel.
I tried the Add-in that was posted here by Frank Kabel some time ago,
and
it
is not fitting my needs either.
One thing I would like to avoid is having to have another user of the
workbook have to install an addin, or have access to a Macro in order
to
view, or alter the formatting or contents of the workbook itself.

"Jim Thomlinson" wrote:

Why use VB. You can use regular conditional formatting.
Change from Cell Value is to Formual is and use something like

=FIND("lunch", A1)0

If it finds the string lunch anywhre in the cell it returns true and
formats
the cell. Off can be done in much the same way...

=FIND("off", A1)=0

HTH

"Fleone" wrote:

I would like to have cells in a workbook formatted depending on
their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell
to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would
like
it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I
have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

You can not just record what you are asking for. It requires programming. We
can give you some pointers on how to writ the code but we can't just write it
for you. Your request is a bit to large for that. Here si a general outline
of what you might want. If you understand this then with some tinkering
around you might be able to accompliosh what you want:

public sub FormatCells()
dim rngCurrentCell as range

for each rngcurrentcell in usedrange
if instr(CurrentCell.value, "lunch") 0 then
rngCurrentCell.interior... 'apply your format
endif

if instr(CurrentCell.value, "off") = 0 then
rngCurrentCell.interior... 'apply your format
endif

next rngCurrentCell

end sub

Try playing with this and see if you can get it to go... If you can and you
still have some specific questions then let us know...

HTH

"Fleone" wrote:

Perhaps I am not using the correct terminology. I am making the assumption
that a Macro is not actually Visual Basic programming in Excel, but a
"recording", if you will, of repetitive tasks. If my use of the term VB is
incorrect, I apologize for the confusion.
I would merely like to have cells formatted based on their contents and
Conditional Formatting doesn't fulfill my needs in this instance due to the
limitation in the number of formatting instances that are allowed.
When attempting to use CF+ it would not apply my formatting request over the
range of cells that I specified. They were all concurrent cells (Range
B4:U14) and the CF+ add-in would apply the same formatting to the entire
range. I believe what was occuring is that the CF+ add-in was making a
reference to a single cell in the range, not the entire range.


"Bob Phillips" wrote:

So you want more than 3 conditions, you can't use CFPlus, you don't want to
use a macro (even though the subject says using VB ...).

To quote the proverb, you want your cake and eat it too. Can't be done.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fleone" wrote in message
...
I can't really use Conditional Formatting because I will be exceeding the
3
formats allowed by Excel.
I tried the Add-in that was posted here by Frank Kabel some time ago, and
it
is not fitting my needs either.
One thing I would like to avoid is having to have another user of the
workbook have to install an addin, or have access to a Macro in order to
view, or alter the formatting or contents of the workbook itself.

"Jim Thomlinson" wrote:

Why use VB. You can use regular conditional formatting.
Change from Cell Value is to Formual is and use something like

=FIND("lunch", A1)0

If it finds the string lunch anywhre in the cell it returns true and
formats
the cell. Off can be done in much the same way...

=FIND("off", A1)=0

HTH

"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like
it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I
have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Using VBA.
Add a standard code module and paste the following:

Option Explicit

Enum eColors
Yellow = 10092543
Green = 13434828
Blue = 16777164
Grey = 12632256
Orange = 10079487
End Enum

Sub MyConditionalFormatting(Target As Range)

Dim cell As Range
Dim clr As Long

For Each cell In Target


Select Case UCase(Trim(cell.Value))
Case "LUNCH": clr = eColors.Yellow
Case "OFF": clr = eColors.Blue
Case "HOLIDAY": clr = eColors.Grey
Case "COURSE": clr = eColors.Orange
Case Else: clr = eColors.Green
End Select

cell.Interior.Color = clr

Next

End Sub
Sub test()
MyConditionalFormatting Range("MyData")
End Sub

To test
On a sheet, select a range, name it "MyData" type some values into some
cells in th erange randomly, include: Holiday, Lunch, Off, Course

run the Test procedure. This passes the range "MyData" to the procedure that
then does the formatting.

This example allows four colors, but you can see that its quite easy to
adapt to use more colors.

HTH
Patrick Molloy
Microaoft Excel MVP



"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Ok, I just got back on the forum here, and saw your response Patrick, I will
try this and see what it does.
In the meantime, I found this buried in another workbook that I have been
working with and made some tweaks and it seems to be working. I have a
question about it though.

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C4:U13")) Is Nothing _
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Lunch": .Interior.ColorIndex = 6
Case "Off": .Interior.ColorIndex = 0
Case "Vacation": .Interior.ColorIndex = 34
Case "Call Off": .Interior.ColorIndex = 44
Case "Holiday": .Interior.ColorIndex = 43
Case "Meeting": .Interior.ColorIndex = 35
Case "Project": .Interior.ColorIndex = 36
Case "Training": .Interior.ColorIndex = 37
End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

This is working GREAT for changing any of the cell colors in any worksheet
that I add this code to, so as a method for defeating the conditional
formatting limitation, it is doing exactly what I would expect.
The next step in this would be to have the ability to affect a change to the
font color in each of the cells according to their content. I have already
tried making another entry in the worksheet identical to the one above, but
changing Interior.ColorIndex to Font.ColorIndex. That doesn't work, I recieve
an error on using an "ambigous" <sp? name. When I try to duplicate one of
the lines, and making the same change, it doesn't work either.
It has become a real head scratcher <G

Any insight you can provide would be great!
I will try the code that you have already provided.
Thanks again!


"Patrick Molloy" wrote:

Using VBA.
Add a standard code module and paste the following:

Option Explicit

Enum eColors
Yellow = 10092543
Green = 13434828
Blue = 16777164
Grey = 12632256
Orange = 10079487
End Enum

Sub MyConditionalFormatting(Target As Range)

Dim cell As Range
Dim clr As Long

For Each cell In Target


Select Case UCase(Trim(cell.Value))
Case "LUNCH": clr = eColors.Yellow
Case "OFF": clr = eColors.Blue
Case "HOLIDAY": clr = eColors.Grey
Case "COURSE": clr = eColors.Orange
Case Else: clr = eColors.Green
End Select

cell.Interior.Color = clr

Next

End Sub
Sub test()
MyConditionalFormatting Range("MyData")
End Sub

To test
On a sheet, select a range, name it "MyData" type some values into some
cells in th erange randomly, include: Holiday, Lunch, Off, Course

run the Test procedure. This passes the range "MyData" to the procedure that
then does the formatting.

This example allows four colors, but you can see that its quite easy to
adapt to use more colors.

HTH
Patrick Molloy
Microaoft Excel MVP



"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C4:U13")) Is Nothing _
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Lunch": .Font.ColorIndex = 6
Case "Off": .Font.ColorIndex = 0
Case "Vacation": .Font.ColorIndex = 34
Case "Call Off": .Font.ColorIndex = 44
Case "Holiday": .Font.ColorIndex = 43
Case "Meeting": .Font.ColorIndex = 35
Case "Project": .Font.ColorIndex = 36
Case "Training": .Font.ColorIndex = 37
End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fleone" wrote in message
...
Ok, I just got back on the forum here, and saw your response Patrick, I
will
try this and see what it does.
In the meantime, I found this buried in another workbook that I have been
working with and made some tweaks and it seems to be working. I have a
question about it though.

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C4:U13")) Is Nothing _
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Lunch": .Interior.ColorIndex = 6
Case "Off": .Interior.ColorIndex = 0
Case "Vacation": .Interior.ColorIndex = 34
Case "Call Off": .Interior.ColorIndex = 44
Case "Holiday": .Interior.ColorIndex = 43
Case "Meeting": .Interior.ColorIndex = 35
Case "Project": .Interior.ColorIndex = 36
Case "Training": .Interior.ColorIndex = 37
End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

This is working GREAT for changing any of the cell colors in any worksheet
that I add this code to, so as a method for defeating the conditional
formatting limitation, it is doing exactly what I would expect.
The next step in this would be to have the ability to affect a change to
the
font color in each of the cells according to their content. I have already
tried making another entry in the worksheet identical to the one above,
but
changing Interior.ColorIndex to Font.ColorIndex. That doesn't work, I
recieve
an error on using an "ambigous" <sp? name. When I try to duplicate one of
the lines, and making the same change, it doesn't work either.
It has become a real head scratcher <G

Any insight you can provide would be great!
I will try the code that you have already provided.
Thanks again!


"Patrick Molloy" wrote:

Using VBA.
Add a standard code module and paste the following:

Option Explicit

Enum eColors
Yellow = 10092543
Green = 13434828
Blue = 16777164
Grey = 12632256
Orange = 10079487
End Enum

Sub MyConditionalFormatting(Target As Range)

Dim cell As Range
Dim clr As Long

For Each cell In Target


Select Case UCase(Trim(cell.Value))
Case "LUNCH": clr = eColors.Yellow
Case "OFF": clr = eColors.Blue
Case "HOLIDAY": clr = eColors.Grey
Case "COURSE": clr = eColors.Orange
Case Else: clr = eColors.Green
End Select

cell.Interior.Color = clr

Next

End Sub
Sub test()
MyConditionalFormatting Range("MyData")
End Sub

To test
On a sheet, select a range, name it "MyData" type some values into
some
cells in th erange randomly, include: Holiday, Lunch, Off, Course

run the Test procedure. This passes the range "MyData" to the procedure
that
then does the formatting.

This example allows four colors, but you can see that its quite easy to
adapt to use more colors.

HTH
Patrick Molloy
Microaoft Excel MVP



"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like
it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I
have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.

Patrick,
This works just as you described it would. Here is my only problem with it.
(I am sure it is just because I really don't know what I am doing <G).
The colors you specified in the eColor sub have a number associated with
them that I am unfamiliar with. When I tried changing the color code assigned
to Green to a color index number 4, the cells were all formatted in Black.
Can you tell me where the number codes that you described can be found? If
so, I can assign any number of colors to the eColor sub then correct?

"Patrick Molloy" wrote:

Using VBA.
Add a standard code module and paste the following:

Option Explicit

Enum eColors
Yellow = 10092543
Green = 13434828
Blue = 16777164
Grey = 12632256
Orange = 10079487
End Enum

Sub MyConditionalFormatting(Target As Range)

Dim cell As Range
Dim clr As Long

For Each cell In Target


Select Case UCase(Trim(cell.Value))
Case "LUNCH": clr = eColors.Yellow
Case "OFF": clr = eColors.Blue
Case "HOLIDAY": clr = eColors.Grey
Case "COURSE": clr = eColors.Orange
Case Else: clr = eColors.Green
End Select

cell.Interior.Color = clr

Next

End Sub
Sub test()
MyConditionalFormatting Range("MyData")
End Sub

To test
On a sheet, select a range, name it "MyData" type some values into some
cells in th erange randomly, include: Holiday, Lunch, Off, Course

run the Test procedure. This passes the range "MyData" to the procedure that
then does the formatting.

This example allows four colors, but you can see that its quite easy to
adapt to use more colors.

HTH
Patrick Molloy
Microaoft Excel MVP



"Fleone" wrote:

I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.