Conditional Formatting in Excel Help Please.....

[Willie T]

Newbie to VBA

I need to use Conditional Formatting to format a range of cells to have
Wheat background and Red text (Bold) if a text string is contained in
each cell. For example, all cells in a range that contain the text
string "LLC" or "Inc" or "Corp" or "Corporation" or
"Company" , etc.

If cell E2 contains Acme, LLC
I need to give that cell (E2) the condition formatting
If cell E3 contains John Brown
That cell does not get the condition formatting
If cell E4 contains Joe's Pool Hall, Inc
Cell E4 gets the condition formatting

Thanks to Dave Peterson I know how to dynamically find the last row or
all rows used in a spreadsheet as follows:

Sub MaxRow()
'Count the number of rows used in a worksheet
oRowMax = wks.UsedRange.Rows.Count
oRowMax1 = wks.UsedRange.Rows.Count + 1
End Sub

Thanks Dave...

I know how to manually use conditional formatting within a spreadsheet
but when using formulas in CF all I can find is when a cell is "equal
to" a value. Can someone tell me how to use "contains" in VBA CF
code or if it can be done? I would also like to incorporate the code
(MaxRow) from above if possible.

Thanks in advance for any help.

Willie T

 

[Bob Phillips]

You have to change the Condition 1 dropdown to Formula Is.

Then you use a form ula such as

=OR(ISNUMBER(FIND("LLC",A1)),ISNUMBER(FIND("Inc",A1)),ISNUMBER(FIND("Corp",A
1)),ISNUMBER(FIND("Corporation",A1)))

Don't see where the last row comes into it, you apply this formula to all
selected cells.

--

HTH

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

 

[Willie T]

Thanks Bob for your reply.

I see how your solution works but, I forgot to mention that the data
I'm pulling in is dynamic. The first time I pull the data from the
source file(s) there maybe 1 hence, one line or record of data and the
next time there maybe 3500 lines of data (E2:E3501); therefore, I need
to know the last record.

I also found this other function in another post.

Sub Cond_Format_()
Cells.FormatConditions.Delete
Dim r As Range
Set r = Range(Range("E2"), Range("E6500").End(xlUp))
r.Cells.FormatConditions.Add Type:=xlExpression,
Formula1:="=RC5=""Help"""

''''''''''r.EntireRow.FormatConditions(1).Interior.ColorIndex = 3
r.Cells.FormatConditions(1).Interior.ColorIndex = 3
End Sub

I can follow this function and see how it changes the background to red
of any cell between E2:E6500 where that cell is equal to "Help",
but I can't seem to get the right syntax when combining the 3. The
three being:

1. The MaxRow function
2. The Cond_Format_ function
3. Your solution

I would like to use the oRowMax variable from below with r from above.
Instead of Range("E6500").End(xlUp)) I would like to use oRowMax in
place of "E6500". Remember, I'm a newbie with no prior
programming experience of any find and I'm trying to learn on my own.

Sub MaxRow()
'Count the number of rows used in a worksheet
oRowMax = wks.UsedRange.Rows.Count
oRowMax1 = wks.UsedRange.Rows.Count + 1
End Sub

Instead of

r.Cells.FormatConditions.Add Type:=xlExpression,
Formula1:="=RC5=""Help"""

which only test for the cell to be equal to the string "Help", I
would like to test to see if a cell contains any of the other strings
we discussed above (LLC, Corp, etc.).

I see that the following changes the background to Red and the text to
Blue.

r.Cells.FormatConditions(1).Interior.ColorIndex = 3
r.Cells.FormatConditions(1).Font.ColorIndex = 5

Where can I look up the Color Indexes? Remember I want my background
to be wheat or cornsilk or some color like that.

How can I just specify the color and not the index number. I tried
this below but it gave me black.

r.Cells.FormatConditions(1).Interior.Color = Cornsilk

Thanks for any help in advance.

Willie T - Newbie

 

[Willie T]

I found the Color Indexes in another post by

David McRitchie Feb 16 2004, 6:51 am

Newsgroups: microsoft.public.excel.programming
Subject: Re: Coloring cells thru VBA

Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

Thanks David

 

[Willie T]

This is what I came up with. This is not the most effective code but
this is all I could come up with. Can anyone help
rearrange/consolidate this code to where it would be more effective?
Thanks for any help...

Sub CondFormatOwner2()
' Highlight property that is owned by businesses (LLC, INC, etc.)

Dim r As Range
Dim CFmaxRow As String

MaxRow

CFmaxRow = "E" & oRowMax

Set r = Range(Range("E2"), Range(CFmaxRow).End(xlUp))
Dim strTemp As String
Dim cnt As Integer

'Check for properties owned by CO, etc. (3 char)
cnt = 1
Do While cnt <= oRowMax
strTemp = Right(r.Cells(cnt).Value, 4)
Select Case strTemp
Case " C0"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case " Co"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case Else
r.Cells.Interior.ColorIndex = xlColorIndexNone
r.Cells.Font.Bold = False
End Select
cnt = cnt + 1
Loop


'Check for properties owned by LLC, INC, Inc, etc. (4 char)
cnt = 1
Do While cnt <= oRowMax
strTemp = Right(r.Cells(cnt).Value, 4)
Select Case strTemp
Case " LLC"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case " INC"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case " Inc"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case "Help"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 3
Case Else
r.Cells.Interior.ColorIndex = xlColorIndexNone
r.Cells.Font.Bold = False
End Select
cnt = cnt + 1
Loop

'Check for properties owned by PROP, COMP, Comp, etc. (5 char)
cnt = 1
Do While cnt <= oRowMax
strTemp = Right(r.Cells(cnt).Value, 5)
Select Case strTemp
Case " PROP"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case " Comp"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case " COMP"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case Else
r.Cells.Interior.ColorIndex = xlColorIndexNone
r.Cells.Font.Bold = False
End Select
cnt = cnt + 1
Loop

'Check for properties owned by L L C, etc. (6 char)
cnt = 1
Do While cnt <= oRowMax
strTemp = Right(r.Cells(cnt).Value, 6)
Select Case strTemp
Case " L L C"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 5
Case Else
r.Cells.Interior.ColorIndex = xlColorIndexNone
r.Cells.Font.Bold = False
End Select
cnt = cnt + 1
Loop

'Check for properties owned by "**Error**", etc. (9 char)
cnt = 1
Do While cnt <= oRowMax
strTemp = Right(r.Cells(cnt).Value, 9)
Select Case strTemp
Case "**Error**"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 3
Case Else
r.Cells.Interior.ColorIndex = xlColorIndexNone
r.Cells.Font.Bold = False
End Select
cnt = cnt + 1
Loop

'Check for properties owned by PROPERTIES, etc. (11 char)
cnt = 1
Do While cnt <= oRowMax
strTemp = Right(r.Cells(cnt).Value, 11)
Select Case strTemp
Case " PROPERTIES"
r.Cells(cnt).Interior.ColorIndex = 19
r.Cells(cnt).Font.Bold = True
r.Cells(cnt).Font.ColorIndex = 3
Case Else
r.Cells.Interior.ColorIndex = xlColorIndexNone
r.Cells.Font.Bold = False
End Select
cnt = cnt + 1
Loop

End Sub