Macro to cover entire workbook

[Datatellsall2]

Hi There,

I have the following macro to highlight cells which include 2 consecutive words that start with a capitalized letter. I would like for this macro to cover my entire workbook and not a specific range. Do I only need to change what is in red? Or is there more to it?

Sub HighlightCellsWithTwoOrMoreUpperCaseLetters()

Dim Cell As Range, DataRange As Range

Set DataRange = Range("A1:A100")

DataRange.ClearFormats

For Each Cell In DataRange

If Cell Like "*[A-Z]*[A-Z]*" Then Cell.Interior.Color = vbYellow

Next

End Sub


Thanks so much,
M

 

[Fluff]

If you mean for it to work on the entire sheet, just change that line to

Set DataRange = ActiveSheet.UsedRange

If you want it to work on all sheets, will it be the same range on each sheet?

 

[Datatellsall2]

Thank you. That's helpful!

Do you know if there is a way to have a macro highlight fields that have two consecutive words with the first letter being capitalized? Right now this macro captures anything with two or more upper case letters.

I'm hoping this maro would catch something like:

I.e. Susan Smith
i.e. We went to the beach with Susan Smith

I assume in excel it's not possible to highlight the individual text, but that would be preferred. If not, highlighting the entire cell is fine.

Thanks again,
M

 

[Fluff]

It is possible to highlight individual words, but it tends to be slow, especially if you have a lot of data