For each cell in Selection?

[metahari]

I have created a macro to highlight the cells in my file if they exceed 55chars. (So I can decide how to abbr the cells myself) If the cell is fine(before or after I fix it), then the macro sets the cell back to no color.

The macro actually works, but I have two issues.
1) I can't seem to make the macro work for each cell in a selection. It seems to count the characters of all cells and highlights the whole block, including blank cells.
2) I'm not sure how to avoid checking blank cells in the first place to speed up the macro.


Sub over55color()
'
' over55color Macro - will turn a row yellow if over 55 characters, will turn it back to white if the cell is fixed and macro runs again

Dim myVariable As Integer
myVariablecount = Len(ActiveCell)
If myVariablecount > 55 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
Selection.Interior.ColorIndex = xlNone
End If
End Sub

but this doesn't seem to work:
For Each cell In Selection
myVariablecount = Len(ActiveCell)
If myVariablecount > 55 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
Selection.Interior.ColorIndex = xlNone
End If
Next cell
End Sub

--Metahari

 

[metahari]

oops, I meant to say this is for Excel 2000.

--Metahari

 

[pbrodsky]

You are iterating over each cell in the selection, but never referring to the cells in the range.
You can use either:
for each cell in selection
cell.select
...

or
for each cell in selection
myvariablecount=len(cell)
...

note that in "for each cell in selection" cell is a variable name - not an object.

 

[xlhelp]

or a small change in the sub will do it

Dim myVariablecount
For Each Cell In Selection
myVariablecount = Len(ActiveCell)
If myVariablecount > 55 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
Selection.Interior.ColorIndex = xlNone
End If
Next Cell

Member- AAAA Association Against Acronym Abusers

 

[CBasicAsslember]

Why not use conditional formatting?

Select 'Formula Is'

Put this in the box next to it.

=IF(LEN(A1)>55,TRUE,FALSE)

Click on the 'Format' button and select how you want to highlight it. You can then either copy-paste or use the format painter to do all the cells you need.

 

[xlbo]

As a general point, I would advise against using words like Cell or Range or Sheet as variables as they may cause conflicts with reserved object names

If you want to make it obvious what you are doing, use:

Rather than
For each cell in Selection

use
For each Cel in Selection


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Metahari:

For future reference, macro questions should be posted in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

But, since you posted in a non-vba forum, I'm with CBasicAsslember. Why not use built-in functionality? Even if you want to use a macro, it would be faster to use code to control Conditional Formatting than to loop through your cells.

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

Help us help you. Please read FAQ181-2886 before posting.

 

[metahari]

Wow, thanks! Ok, now it goes through the selected cells
I used cell.select from pbrodsky.

What about not checking blank cells, or... haha, I hit select all and had to escape out as it started checking to infinity. So, perhaps a way to only check the rows and columns that exist?


--Metahari

 

[metahari]

oops, so sorry I'm in the wrong forum, feel free to move the thread if you want.

I don't fully understand how to make conditional formatting.

--Metahari

 

[AnotherHiggins]

No problem. Like I said, just for future reference.

To use Conditional Formatting:
-Select the column that contains the entries.
-Go to Format > Conditional Formatting.
-Change the first box to Formula Is.
-In the second box, type in [COLOR=blue white]=len(A1)>55[/color] (replace "A" with whatever column you are using)
-Press the Format button.
-Go to the Patterns tab.
-Pick a pleasing color.

That's it! If you want to do that in a macro, just turn on the macro recorder (Tools > Macro > Record New Macro) first, then follow those steps.

CBasicAsslember: You don't need to wrap the formula in an IF. Just using =len(a1)>55 will return TRUE if true and FALSE if false.

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

Help us help you. Please read FAQ181-2886 before posting.