How do I create a VB to format a 3 adjacent columns?

Question

0.00/5 (No votes)

See more:

hi everyone i have a spreadsheet that i wanted to format and create a code. it will be based on the first 3 columns. if all 3 will contain the words Waiting Waiting Complete in that order, i want it to show with strikethrough and red fonts. i tried the code below but it seems its not running.

What I have tried:

VB

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Range("A:C")
    With r.Font
        If Range("A:C") = "Waiting" And "Complete" Then
            .Strikethrough = True
            .Color = 255
        Else
            .Strikethrough = False
            .ColorIndex = xlAutomatic
        End If
    End With
End Sub

Posted 5-May-16 2:59am

Shilos_Dad

Updated 5-May-16 3:39am

CHill60

v2

Add a Solution

2 solutions

Solution 2

Your if condition is wrong. You cannot compare a range of columns to a string and get anything other than False. Also, your use of the keyword And is wrong. You must have a boolean expression on both sides of the And. It is never used to compare something to this And that.

You have to look in every row in the range you want and compare the cell values to whatever string you're looking for. Depending on the number of rows, this may take a while.

Perhaps a better solution would be to conditional formatting?

Posted 5-May-16 3:39am

Dave Kreskowiak

Updated 5-May-16 3:41am

v2

Comments

CHill60 5-May-16 10:04am

5'd - I've updated my solution with the Conditional formatting steps

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-05-05T03:38:00

The code must be placed in the VBA for the sheet in question. If this is not running at all (which I'm guessing it's not as you haven't mentioned the errors) then you must have put it in
- the wrong worksheet, or
- the VBA sheet for ThisWorkBook, or
- a VBA module

When you put it in the right place and enter some data on the sheet you will immediately get an Error 13 Type Mismatch on this line

VB

If Range("A:C") = "Waiting" And "Complete" Then

The reason for that is because "Complete" is not a Boolean expression. It should be

If Range("A1") = "Waiting" And Range("B1") = "Waiting" And Range("C1") = "Complete" Then

The next problem is that this routine only ever looks at Row 1 on the WorkSheet. You should use Target to determine the range for the Font. This works

VB.NET

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rwRange As String
    rwRange = "A" + CStr(Target.Row) + ":C" + CStr(Target.Row)

    Dim r As Range
    Set r = Range(rwRange)
    With r.Font
        If r.Cells(1, 1) = "Waiting" And r.Cells(1, 2) = "Waiting" And r.Cells(1, 3) = "Complete" Then
            .Strikethrough = True
            .Color = 255
        Else
            .Strikethrough = False
            .ColorIndex = xlAutomatic
        End If
    End With

End Sub

[EDIT]
See the comment from @Dave-Kreskowiak in his solution...

Quote:
Perhaps a better solution would be to conditional formatting?

I think he is spot-on ...
- it will perform better
- if that was the only bit of VBA in your worksheet then removing it will improve the size on disk of the workbook
- No issues if Users turn off macros

If you do go down that route then
- highlight columns A through C,
- select Conditional Formatting,
- New Rule
- "Use a formula to determine which cells to format"
- Enter this formula

=AND(AND($A1="Waiting", $B1="Waiting"),$C1="Complete")

- Click Format
- Choose Font Color and Strikethrough
- Hit "Ok" through the dialogs until complete