excel macro needed

I have a column in a spreadsheet in which the cells will have value one of the following :
m = male,f = female
m = male^f = female
Note that there is space after and before equal to sign.
I need a macro which will check for all the cells in the column so that there is space after and before equal to sign. If a space is not there then that cell should be colured in red.
michaelm702Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
michaelm702,
Try  using Conditional Formatting instead of a macro for your highlighting. To create Conditional Formatting, select all the cells that will receive the formatting, then open the Format...Conditional Formatting menu item.
Select "Formula Is" in the left field, then enter the following formula:
=NOT(ISERROR(FIND("=",SUBSTITUTE(A1," = ",""))))                   Formula assume that cell A1 is at top left corner of selected range

Click the Format button, then the Patterns tab. Choose your red highlight color, then click OK. Or else choose the Font tab and the red font color.

If the results don't appear quite right, check the Conditional Formatting formula to make sure that Excel didn't add extra quotes or absolute references in its zeal to be helpful. Correct any such errors, then click OK again.
Brad
0
 
GrahamSkanRetiredCommented:
Trusting that you have two equal signs in each cell:

    Dim v
    Dim r As Integer
   
    Const c = 1
    For r = 1 To ActiveSheet.UsedRange.Cells.Count
        v = Split(ActiveSheet.UsedRange.Cells(r, c), " = ")
        If UBound(v) < 2 Then
            ActiveSheet.UsedRange.Cells(r, c).Interior.ColorIndex = 3
        End If
    Next r
0
 
michaelm702Author Commented:
Each Cell does not have only two equal to signs there must be atleast one and any number greater than one. We should also check that each cell in that column has an equal to present aso.
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
   Dim v
    Dim r As Integer
    Dim n as integer
    Const c = 1
    For r = 1 To ActiveSheet.UsedRange.Cells.Count
        v = Split(ActiveSheet.UsedRange.Cells(r, c), " = ")
        n = ubound(v, "=")
        If n < 1 then
             ActiveSheet.UsedRange.Cells(r, c).Interior.ColorIndex = 3
        Else
        v = Split(ActiveSheet.UsedRange.Cells(r, c), " = ")
        If UBound(v) < n Then
            ActiveSheet.UsedRange.Cells(r, c).Interior.ColorIndex = 3
        End If
End if
    Next r
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.