An easier way to write this sub-routine

Posted on 2013-10-21
Medium Priority
Last Modified: 2013-10-26
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"
        Range("D9").Value = "Error"
        Range("D9").Font.Color = vbYellow
        Range("D9").Interior.Color = vbBlack
        Range("D9").Locked = True
        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"
        Range("D10").Value = "Error"
        Range("D10").Font.Color = vbYellow
        Range("D10").Interior.Color = vbBlack
        Range("D10").Locked = True
        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"
        Range("D11").Value = "Error"
        Range("D11").Font.Color = vbYellow
        Range("D11").Interior.Color = vbBlack
        Range("D11").Locked = True
        Exit Sub
    End If

Open in new window

Question by:Frank Freese
  • 5
  • 2
LVL 41

Assisted Solution

als315 earned 1000 total points
ID: 39588475
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"
        .Value = "Error"
        .Font.Color = vbYellow
        .Interior.Color = vbBlack
        .Locked = True
        Exit For
      End If
    End With
Next i

Open in new window

LVL 39

Accepted Solution

nutsch earned 1000 total points
ID: 39588480
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"
        Cells(lRowLoop, 4).Value = "Error"
        Cells(lRowLoop, 4).Font.Color = vbYellow
        Cells(lRowLoop, 4).Interior.Color = vbBlack
        Cells(lRowLoop, 4).Locked = True
        Exit Sub
    End If
Next lRowLoop

Open in new window


Author Comment

by:Frank Freese
ID: 39588526
WOW - looks like two great solutions with different approaches.
If I have a problem I'll follow up
Thank you very much.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 39

Expert Comment

ID: 39588664
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.



Author Comment

by:Frank Freese
ID: 39588682
You're absolutely correct - not by design. Now where is the Request Attention link (I'm looking)?

Author Comment

by:Frank Freese
ID: 39588697
I found it!

Author Comment

by:Frank Freese
ID: 39588707
I just asked to correct my mistake. I'm truly sorry for the error and I appreciate you bringing it to my attention.

Author Comment

by:Frank Freese
ID: 39589421
Let me know if you do not get your points.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question