An easier way to write this sub-routine

Folks,
Examine the code below. I have 14 of these to do they way I am doing it. If this is the best way, OK. If not, I'd like to reduce the amount of code. My range is from C9:C23 and D9:D23:
 If Range("C9").Text = "FALSE" Then
        Range("D9").Locked = False
        Range("D9").Font.Color = vbWhite
        Range("D9").Interior.Color = vbRed
        Range("D9").Value = "Correct"
    Else
        Range("D9").Value = "Error"
        Range("D9").Font.Color = vbYellow
        Range("D9").Interior.Color = vbBlack
        Range("D9").Locked = True
        CheckFormulaFunction
        Exit Sub
    End If
    
     If Range("C10").Text = "FALSE" Then
        Range("D10").Locked = False
        Range("D10").Font.Color = vbWhite
        Range("D10").Interior.Color = vbRed
        Range("D10").Value = "Correct"
    Else
        Range("D10").Value = "Error"
        Range("D10").Font.Color = vbYellow
        Range("D10").Interior.Color = vbBlack
        Range("D10").Locked = True
        CheckFormulaFunction
        Exit Sub
    End If
    
     If Range("C11").Text = "FALSE" Then
        Range("D11").Locked = False
        Range("D11").Font.Color = vbWhite
        Range("D11").Interior.Color = vbRed
        Range("D11").Value = "Correct"
    Else
        Range("D11").Value = "Error"
        Range("D11").Font.Color = vbYellow
        Range("D11").Interior.Color = vbBlack
        Range("D11").Locked = True
        CheckFormulaFunction
        Exit Sub
    End If

Open in new window

Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Try this sub:
Dim i As Integer
For i = 9 To 23
    With Range("D" & i)
      If Range("C" & i).Text = "FALSE" Then
        .Locked = False
        .Font.Color = vbWhite
        .Interior.Color = vbRed
        .Value = "Correct"
      Else
        .Value = "Error"
        .Font.Color = vbYellow
        .Interior.Color = vbBlack
        .Locked = True
        CheckFormulaFunction
        Exit For
      End If
    End With
Next i

Open in new window

0
nutschCommented:
You can put it in a loop as follows, but you could also put an if structure and some conditional formatting to get most of the same results (outside of the locked cell)

Dim lRowLoop As Long

For lRowLoop = 9 To 23

 If Cells(lRowLoop, 3).Text = "FALSE" Then
        Cells(lRowLoop, 4).Locked = False
        Cells(lRowLoop, 4).Font.Color = vbWhite
        Cells(lRowLoop, 4).Interior.Color = vbRed
        Cells(lRowLoop, 4).Value = "Correct"
    Else
        Cells(lRowLoop, 4).Value = "Error"
        Cells(lRowLoop, 4).Font.Color = vbYellow
        Cells(lRowLoop, 4).Interior.Color = vbBlack
        Cells(lRowLoop, 4).Locked = True
        CheckFormulaFunction
        Exit Sub
    End If
    
Next lRowLoop

Open in new window


Thomas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
WOW - looks like two great solutions with different approaches.
If I have a problem I'll follow up
Thank you very much.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

nutschCommented:
Thanks for the grade, but I think your mouse slipped when you accepted only my solution, especially since you als315's was first and mine was very similar (such that I wouldn't have posted it, if I had seen his first).

If you agree, please follow the Request Attention link and ask for the question to be reopened so you give all or some of the credit where it's due.

Thanks,

Thomas
0
Frank FreeseAuthor Commented:
You're absolutely correct - not by design. Now where is the Request Attention link (I'm looking)?
0
Frank FreeseAuthor Commented:
I found it!
0
Frank FreeseAuthor Commented:
I just asked to correct my mistake. I'm truly sorry for the error and I appreciate you bringing it to my attention.
0
Frank FreeseAuthor Commented:
Thomas,
Let me know if you do not get your points.
Frank
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.