find cells based on its content's format

[jsd219]

Does anyone know how to search for cells based on a text format.

below is an example:

Cell 1 - JACK JUMPED OVER
Cell 2 - -JILL FELL DOWN

i am trying to write a script that will search a specific column, once
it finds these cells all cells with all its contents in all CAPS, it
places an "x" in another specified column.

Any help would be great appreciated.

PS. i know i can do a conditional find but i need to make it into a
script.

God bless
jsd219

 

[Guest]

Enter this small macro:

Sub case_check()
'gsnu 10_28_2006
Dim s As String, r As Range
For Each r In Selection
If r.Value = UCase(r.Value) Then
r.Offset(0, 1).Value = "x"
End If
Next
End Sub

Select the cells you want to check and run it. It uses the next column over
to do the marking, You can change the OFFSET() to get to any desired column.

 

[jsd219]

Thank you Gary;s Student,

I am checking column "N"

What i would like to do is select a cell in column "N" and have the
script run through the rest of column "N" to the end opf the spread
sheet but NOT go above where i selected.
i.e if my sheet is 5000 rows and I select "N200" i need the scrip to
start at N200 and go to N5000 but never check N199 - N1.

can you help me with this as well?

God bless
jsd219

 

[Guest]

Sub case_check2()
'gsnu 10_28_2006
Dim s As String, r As Range

nlastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
myrow = Selection.Row
howmany = nlastrow - myrow

Set r2 = Range(Selection, Selection.Offset(howmany, 0))
r2.Select

For Each r In Selection
If r.Value = UCase(r.Value) Then
r.Offset(0, 1).Value = "x"
End If
Next
End Sub


In this version, you select a single cell and the code processes down the
column from where you indicated. The code will not look above your indicated
cell.

 

[jsd219]

Thank you i really appreciate it. would by any chance mind explaining
the code for me. idon't understand the r2. what does the "2" represent?

God bless
jsd219

 

[Guest]

The 2 is just part of the name of the variable. I could just as easily used
rtwo or r_two.