Cell color

I need to be able to highlight a cell if it contains a hyphen or dash "-".  This is what I have, but does not work. Thanks in advance!

Sheets("View").Activate
 
   Range(Range("I1"), Range("I1").End(xlDown)).Select
   For Each cell In Selection
      If cell = "-" Then cell.EntireRow.Interior.ColorIndex = 4
   Next cell
leezacAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Actually I guess I should ask what you mean when you say it doesn't work? Does anything happen? Do you want to color the row if the cell contains something like "blah-blah"?
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
If Instr(1, cell, "-") Then cell.EntireRow.Interior.ColorIndex = 4
0
 
byundtConnect With a Mentor Commented:
Are the hyphens appearing in the cell as a result of formatting? If so, you need to specify Cell.Text in your macro. If they are manually entered, your existing code should work as desired.

The following modification of your code tests Cell.Text and uses InStr as suggested by MartinLiss.
Sub test()
Dim cell As Range, rg As Range
With Sheets("View")
   Set rg = Range(.Range("I1"), .Range("I1").End(xlDown))
   For Each cell In rg.Cells
      If InStr(1, cell.Text, "-") > 0 Then cell.EntireRow.Interior.ColorIndex = 4
   Next
End With
End Sub

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rob HensonConnect With a Mentor Finance AnalystCommented:
You could also achieve the formatting with Conditional Formatting with a custom CF with formula:

Select the required cells (assuming column I) and choose the Conditional Formatting wizard and enter the following as a formula condition:

=FIND("-",I1,1)>0

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
Just realised you wanted the whole row coloured, so slight change.

Select the whole area and the CF formula would then be:

=FIND("-",$I1,1)>0

Only change being the addition of the $ in front of I to ensure that CF in all columns are looking at Column I.

Thanks
Rob H
0
 
leezacAuthor Commented:
No nothing works.  I entered the hypen manually to test but it is entered via a macro if the cell is null.  I tried conditional formating but it is deleted somehow when the file is updated.
0
 
Martin LissOlder than dirtCommented:
Can you attach your workbook and describe the steps to take to reproduce the problem?
0
 
leezacAuthor Commented:
I also do not want row colored - just cell
0
 
Martin LissOlder than dirtCommented:
Can you attach your workbook and describe the steps to take to reproduce the problem?
0
 
leezacAuthor Commented:
Decided to use a "0' instead of '-" and it works now with conditional formatting.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.