Need help creating macro with conditional formatting

[Photo.Man.82]

Hey guys, I'm trying to set up a macro using conditional formatting so
that an entire row of a worksheet will be highlighted if a particular
column's cell has the word "yes" in it.

For example, if there are any cells in column T that have the word
"yes" in them, I'd like Excel to automatically highlight that row
yellow. I found a solution online, but I need help tweaking it.
Here's what I've got:

Sub Highlight()
[T1].Activate
With Range([T1], [T65536].End(xlUp)).EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$T1=""yes"""
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub


I'd like to change the range function to search every column in the
worksheet, instead of just column T. I just don't know enough about
VBA to do this.

Also, let's say I wanted to search three columns, like R, S, and T,
instead of every single column, how would the macro be written? Thanks
in advance!

-Drew

 

[JE McGimpsey]

I'd like to change the range function to search every column in the
worksheet, instead of just column T. I just don't know enough about
VBA to do this.

One way:

Public Sub AnyCellYesRowYellow()
With Cells.FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=COUNTIF(R[]:R[],""yes"")"
.Item(1).Interior.ColorIndex = 6
End With
End Sub

Also, let's say I wanted to search three columns, like R, S, and T,
instead of every single column, how would the macro be written? Thanks
in advance!

One way:

Public Sub AnyCellinRSorTYesRowYellow()
With Cells.FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=COUNTIF(R[]C18:R[]C20,""yes"")"
.Item(1).Interior.ColorIndex = 6
End With
End Sub

 

[Geoff Lilley]

Another way?

Sub Highlight()
'figure out what the last row is in the range of cells
Dim intLastRow As Integer
'assign a temporary variable to hold the value of the last cell in
Column T
Dim varLastCell As Variant
'find the last row
intLastRow = ActiveSheet.UsedRange.Rows.Count
'set the last cell variable
varLastCell = "T" & intLastRow
'select the range
Range("R1:" & varLastCell).Select
'delete the conditional formats, then add the yellow for "Yes"
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=R1=""yes"""
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

Just wondering what people thought. Wouldn't want to disrespect my
hero Mr. McGimpsey, ever.

I'd like to change the range function to search every column in the
worksheet, instead of just column T. I just don't know enough about
VBA to do this.

One way:

Public Sub AnyCellYesRowYellow()
With Cells.FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=COUNTIF(R[]:R[],""yes"")"
.Item(1).Interior.ColorIndex = 6
End With
End Sub

Also, let's say I wanted to search three columns, like R, S, and T,
instead of every single column, how would the macro be written? Thanks
in advance!

One way:

Public Sub AnyCellinRSorTYesRowYellow()
With Cells.FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=COUNTIF(R[]C18:R[]C20,""yes"")"
.Item(1).Interior.ColorIndex = 6
End With
End Sub

 

[JE McGimpsey]

Just wondering what people thought. Wouldn't want to disrespect my
hero Mr. McGimpsey, ever.

Posting alternative solutions is always appropriate and never
disrespectful. There's nearly always an alternative way to do something
in XL/VBA.

Since you asked, I've a couple of comments:

1) Declaring intLastRow as an Integer is inviting trouble, as Integers
can only hold values up to 32767. Since a current XL worksheet can hold
65536 rows, and XL12 will allow over a million, this could yield an
unanticipated (and silent) error if the user had more than 32K entries.

2) Since varLastCell will be assigned a string value, my preference
would be to declare it as a String.

3) UsedRange should generally *not* be used in the way you do unless you
have total control over the worksheet. for instance, create a fresh
worksheet and enter "yes" in T4. Run your macro. The cell/row will not
be highlighted, because the UsedRange will consist entire of cell T4
(which only has 1 row, so your selection will be Range("R1:T1")). If row
1 is blank, then your format will be applied to the wrong row.

4) The original post indicated that the CF was to highlight the entire
row if "yes" were found in the target column(s). Your method highlights
the cell containing "yes" only. A modification could be made, of course.

5) In general, I don't use Selections - they're generally slower, and
the code is much harder to maintain. Using Selections can also really
interfere with the user's ability to operate the worksheet if they're
working in a cell or range and the macro changes that selection. One
workaround would be to save the selected range and restore it at the end
of the macro.

Keep posting!

 

[Photo.Man.82]

Thanks so much guys for the quick responses this weekend. Mr.
McGimpsey, I used your first response and it works perfect!

I've actually got a couple of other questions (if you don't mind)
regarding another formula that I need help incorporating into a macro.

I'm using the following formula to find out whether or not the value
located in cell L1 is greater than or equal to 10000. If it is, it
marks yes in another cell (T for example), and if not, it leaves it
blank.

=IF(L1>=10000,"yes","")

There are a few problems:

1) L1 won't always be the cell that needs to be checked
2) 10000 isn't always the value that needs to be input
3) T won't always be the column that the formula returns yes into.

Is there a way to create a macro that prompts the user for that info?
That way it can auto-populate the field and highlight it all at once.
The work I'm doing is very tedious and long-lasting, so it'd be nice to
be able to automate it.

Anyway, let me know what you guys think and if you have some pointers,
I'd appreciate some help. =)

Thanks again!

-Andrew

 

[Geoff Lilley]

I'm going to throw my hat in the ring on this one, but I hope the more
experienced members will help this along:

Sub checkCell()
Dim strColtoCheck As String
Dim strColtoChange As String
Dim lngValuetoCheck As Long
Dim strCelltoCheck As String
Dim strCelltoChange As String
strColtoCheck = InputBox("What column has the value you want to
evaluate?")
strColtoChange = InputBox("What column has the value you want to
conditionally format?")
lngValuetoCheck = InputBox("What value are you looking for?")

strCelltoCheck = Range(strColtoCheck & ActiveCell.Row).Value
strCelltoChange = Range(strColtoChange & ActiveCell.Row).Value
strCelltoChange = strColtoChange & ActiveCell.Row
If strCelltoCheck >= lngValuetoCheck Then
Range(strCelltoChange).Value = "Yes"
Else
strCelltoChange = ""
End If

End Sub

HTH. I would appreciate input from other, more experienced posters on
how to improve.

Cheers,
Geoff

 

[JE McGimpsey]

=IF(L1>=10000,"yes","")

There are a few problems:

1) L1 won't always be the cell that needs to be checked
2) 10000 isn't always the value that needs to be input
3) T won't always be the column that the formula returns yes into.

Is there a way to create a macro that prompts the user for that info?
That way it can auto-populate the field and highlight it all at once.
The work I'm doing is very tedious and long-lasting, so it'd be nice to
be able to automate it.

One way:

Public Sub EnterVariableFormula()
Const sBASE_FORMULA As String = "=IF(%%<=##,""yes"","""")"
Const sINPUT_TITLE As String = "Create Formula"
Const sPROMPT1 As String = "Enter or choose cell to be checked:"
Const sPROMPT2 As String = "Enter maximum value for cell %%:"
Const sPROMPT3 As String = _
"Enter or choose the cell to contain the formula:"
Const sSINGLECELL As String = "You must choose a single cell!"

Dim vTestCell As Variant
Dim vMaxValue As Variant
Dim vFormulaCell As Variant

With Application
Set vTestCell = Nothing
Do
On Error Resume Next
Set vTestCell = .InputBox( _
Prompt:=sPROMPT1, _
Title:=sINPUT_TITLE, _
Default:="", _
Type:=8)
On Error GoTo 0
If vTestCell Is Nothing Then Exit Sub 'User cancelled
If vTestCell.Count > 1 Then
MsgBox sSINGLECELL
Set vTestCell = Nothing
End If
Loop Until Not vTestCell Is Nothing

vMaxValue = .InputBox( _
Prompt:=.Substitute(sPROMPT2, _
"%%", vTestCell.Address(False, False)), _
Title:=sINPUT_TITLE, _
Default:="", _
Type:=1)
If VarType(vMaxValue) = vbBoolean Then Exit Sub 'User cancelled

Set vFormulaCell = Nothing
Do
On Error Resume Next
Set vFormulaCell = .InputBox( _
Prompt:=sPROMPT3, _
Title:=sINPUT_TITLE, _
Default:="", _
Type:=8)
On Error GoTo 0
If vFormulaCell Is Nothing Then Exit Sub 'User cancelled
If vFormulaCell.Count > 1 Then
MsgBox sSINGLECELL
Set vFormulaCell = Nothing
End If
Loop Until Not vFormulaCell Is Nothing
vFormulaCell.Formula = .Substitute(.Substitute( _
sBASE_FORMULA, "%%", _
IIf(vTestCell.Parent.Name <> vFormulaCell.Parent.Name, _
vTestCell.Parent.Name & "!", "") & _
vTestCell.Address(False, False)), "##", vMaxValue)
End With
End Sub



Note that this has only *very* rudimentary error checking - for
instance, if the user chooses a different workbook for either vTestCell
or vFormulaCell, that choice won't be included in the formula.

 

[Photo.Man.82]

Wow, you guys are brilliant! This will take such a huge load off my
chest. Thank you so much, in particular to Mr. McGimpsey--you guys
have been very very helpful. Just out of curiousity, what do you do
for a living? And also, do you take cash, or do you prefer check? ;-)

I'll definitely be following these usenet groups a lot closer now.
Does most of the VBA/Macro info take place in this group or in the
"microsoft.public.excel.programming" forum?

Thanks again!

-Drew

 

[JE McGimpsey]

Just out of curiousity, what do you do for a living? And also, do
you take cash, or do you prefer check? ;-)

I'm an engineering consultant and programmer. I prefer checks, but cash
is OK too...

I'll definitely be following these usenet groups a lot closer now.
Does most of the VBA/Macro info take place in this group or in the
"microsoft.public.excel.programming" forum?

For general programming questions, m.p.e.p is more likely to yield a
quick reply. However, the VAST majority of posters there use WinXL, so
make sure you specify that you're using MacXL - i.e., VBA5, no ActiveX,
etc.