Excel Macro to Highlight Row 2

[dpgirl]

I'm not sure if this is the right forum, but I need help with an Excel Macro. I'm interested in creating a macro that will go through column A of a spreadsheet. If the value in column A equals "Total" (not case-sensitive), then the macro will highlight the entire row in yellow. Could anyone be so kind as to help me with this? Is it possible to write the macro so that it runs on every tab in a spreadsheet? Thanks in advance!

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpgirl]

Here is what I'm starting with (which I copied from some other posting):

Sub Highlight1()
[G3].Activate
With Range([f1], [f65536].End(xlUp)).EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$f1=""Full-Line"""
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub


My problems:
- I originally wanted to highlight the entire row, but I've changed my mind and only want it to highlight columns F through J of that row. How do I do this?
- How do you change the criteria such that it looks for any cell where the phrase "Full-Line" is contained within the cell? Right now, the script looks for an exact value of "Full-Line". Can I use wildcards?

 

[AnotherHiggins]

I know you posted in the VBA forum, but, you know, teach a man to fish and all that....

Turn on your macro recorder (Tools > Macros > Record New Macro) and do the conditional formatting the way you want. Observe the code that was generated.

Here's how you'll do what you want (without VBA because the macro recorder will pick that up for you):

- Select Columns F:J - make sure that row 1 is the active cell (the one that doesn't appear to be shaded like all the other selected cells)

- Go to Format > Conditional Formatting

- Change the first box to Formula Is

- Type in [COLOR=blue white]=IsNumber(Search("total",$A1))[/color]

- Select Format

- Go to the Patterns tab

- Select whatever color you want to highlight the cells (I recommend the light yellow - easier on the eyes than bright yellow)

That's it. Now look at the code that was generated.

*NOTE: The Find function is case-sensitive, the Search function is not.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[slowmike]

Somewhat along these lines, is there a way to highlight all the cells that contain a value within a list of values? So far I have:

Sub Highlight()
findme = Split(Range("c7"), ",")
For q = 0 To UBound(findme)
  Columns("a:a").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:=findme(q)
    Selection.FormatConditions(1).Interior.ColorIndex = 36

Next q

End Sub

where cell C7 contains a list of values I want to highlight in column A. The problem is I am limited to 3 formulas (formulae?). Any suggestions?

 

[SkipVought]

"...C7 contains a list of values I want to highlight in column A"

A single cell cannot contain a valid list. A range of cells can.

Use the MATCH function and ISNA function to determine which cells match the list.
[tt]
=ISNA(MATCH(A1,YouListRange,0))
[/tt]
in the Formula is textbox



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ck1999]

This worked in Excel 2007:

Sub Highlight()

findme = Split(Range("c7"), ",", -1)
  Columns("a:a").Select
    Selection.FormatConditions.Delete
For q = 0 To UBound(findme)

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:=findme(q)
    Selection.FormatConditions(q + 1).Interior.ColorIndex = 36

Next q

End Sub

c7 had a list without spaces after the comma. Spaces after the comma would not give the same results.

ck1999

 

[slowmike]

Great. Thanks for the help. Now, is there a way to freeze the formatting so that if I put a new list in c7, the previously highlighted (highlit?) cells remain so? I tried copying and pasting formats, but that did not work. Thanks again.

 

[ck1999]

This code removes previous formats.

    Selection.FormatConditions.Delete

if you remove this line from the code it will not delete the previous formatingconditions

ck1999

 

[firefytr]

This may be a silly question, but why do you need code for this??

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[slowmike]

I have a list of items that I need to check. I also receive a list (comma-delimited) of items that have already been checked, so I am trying to design a macro that cycles through the second list and highlights the items in the original list so I know that they are OK.

 

[slowmike]

I'm trying to find a way to work around the limit of 3 conditional formatting formulae. In other words, if there are more than 3 items in the comma-delimited list, I get a application error. Any ideas?

 

[AnotherHiggins]

slowmike,

Please create a new thread for each additional, different question you have. That makes it much easier for others to search through threads later on.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.