Vba to execute what was done with conditional formatting

[ponderance]

I apologize for the basic problem. Brain abandoned me.

I am looking for a vba script to apply the following formulas with colored backgrounds noted after

=$g1>"h*" 'light red
=$n1>"ps*" 'light blue
=$n1>"cs*" 'light blue
=$ac1<1 'grey

The final goal is to use the macro to color lines based on values without having to write conditional formatting code hundreds of times a day. Any help is much appreciated

[Kenneth Hobs]

Welcome to the forum!

Maybe something like:

Sub CColors()    
    Dim a(1 To 3), b, c, i As Integer
    ReDim b(1 To UBound(a))
    ReDim c(1 To UBound(a))


    a(1) = "A2": b(1) = Evaluate(a(1) & "> 5"): c(1) = vbYellow
    a(2) = "A3": b(2) = Evaluate(a(2) & "> 10"): c(2) = vbRed
    a(3) = "A4": b(3) = Evaluate(a(3) & "<= 5"): c(3) = RGB(0, 0, 255)  'Blue
    
    For i = 1 To UBound(a)
        Range(a(i)).Interior.Color = xlNone
        If b(i) Then Range(a(i)).Interior.Color = c(i)
    Next i
End Sub

[ponderance]

Welcome to the forum!

Maybe something like:

Sub CColors()    
    Dim a(1 To 3), b, c, i As Integer
    ReDim b(1 To UBound(a))
    ReDim c(1 To UBound(a))


    a(1) = "A2": b(1) = Evaluate(a(1) & "> 5"): c(1) = vbYellow
    a(2) = "A3": b(2) = Evaluate(a(2) & "> 10"): c(2) = vbRed
    a(3) = "A4": b(3) = Evaluate(a(3) & "<= 5"): c(3) = RGB(0, 0, 255)  'Blue
    
    For i = 1 To UBound(a)
        Range(a(i)).Interior.Color = xlNone
        If b(i) Then Range(a(i)).Interior.Color = c(i)
    Next i
End Sub

Thank you for the welcome!

i altered the code to the following:

Sub CColors()
Dim a(1 To 4), b, c, i As Integer
ReDim b(1 To UBound(a))
ReDim c(1 To UBound(a))

a(1) = "G1": b(1) = Evaluate(a(1) & "> H*"): c(1) = RGB(255, 99, 99) 'red
a(2) = "n1": b(2) = Evaluate(a(2) & "> ps*"): c(2) = RGB(99, 99, 255) 'Blue
a(3) = "n1": b(3) = Evaluate(a(3) & "> cs*"): c(3) = RGB(99, 99, 255) 'Blue
a(4) = "ac1": b(4) = Evaluate(a(4) & "< 1"): c(4) = RGB(150, 150, 150) 'grey

For i = 1 To UBound(a)
Range(a(i)).Interior.Color = xlNone
If b(i) Then Range(a(i)).Interior.Color = c(i)
Next i
End Sub


---- it errors and debug highlights "If b(i) Then"

Run-time error '13':
Type mismatch

[Kenneth Hobs]

You can quickly test in the Immediate window? e.g.

?Evaluate("G1 > H*")

and press enter key after to execute that line. ? is the same-as Debug.Print. It outputs a run results to the Immediate window.

I am not sure why you do that. >< equality comparisons are usually done for numerical values, not Strings. If you are trying to do something else, you might explain what that is if you want help.

When using a quote in a formula string, you have to add the quote. This is called the double quote method.

a(1) = "G1": b(1) = Evaluate(a(1) & "> ""H*"""): c(1) = RGB(255, 99, 99) 'red
    a(2) = "n1": b(2) = Evaluate(a(2) & "> ""ps*"""): c(2) = RGB(99, 99, 255) 'Blue
    a(3) = "n1": b(3) = Evaluate(a(3) & "> ""cs*"""): c(3) = RGB(99, 99, 255) 'Blue
    a(4) = "ac1": b(4) = Evaluate(a(4) & "< 1"): c(4) = RGB(150, 150, 150) 'grey

[ponderance]

You can quickly test in the Immediate window? e.g.

?Evaluate("G1 > H*")

and press enter key after to execute that line. ? is the same-as Debug.Print. It outputs a run results to the Immediate window.

I'll check that. Right now the computer is in the middle of what I assume is an update designed to take forever as a challenge against my patience. So I'll report back on this specific, later.

I am not sure why you do that. >< equality comparisons are usually done for numerical values, not Strings.

The variable is either HD, h0-h5. I guess it could work just as well with = instead. I'll try that.

When using a quote in a formula string, you have to add the quote. This is called the double quote method.

a(1) = "G1": b(1) = Evaluate(a(1) & "> ""H*"""): c(1) = RGB(255, 99, 99) 'red
    a(2) = "n1": b(2) = Evaluate(a(2) & "> ""ps*"""): c(2) = RGB(99, 99, 255) 'Blue
    a(3) = "n1": b(3) = Evaluate(a(3) & "> ""cs*"""): c(3) = RGB(99, 99, 255) 'Blue
    a(4) = "ac1": b(4) = Evaluate(a(4) & "< 1"): c(4) = RGB(150, 150, 150) 'grey

I was wondering about this. I'll try this, too.

If you are trying to do something else, you might explain what that is if you want help.

I'll elaborate on the parent post content:

This is what each of the conditional formatting lines performs, which I'm trying to have done with one vba macro instead

=$g1>"h*" 'light red
This colors all the backgrounds of lines for orders that are on hold.

=$n1>"ps*" 'light blue
This colors all the backgrounds of lines for orders that are printed.

=$n1>"cs*" 'light blue
This colors all the backgrounds of lines for orders that are printed and processed.

=$ac1<1 'grey
This colors all the backgrounds of lines for orders that have 0 stock

I hope this response helps elaborate what I thought I covered on the op.

[ponderance]

I guess I don't understand how to test it in the immediate window. Which field do I use?


changed the script to the following, but now it produces no visible results. no errors, but doesn't seem to change any backgrounds.

------------

Sub CColors()
Dim a(1 To 4), b, c, i As Integer
ReDim b(1 To UBound(a))
ReDim c(1 To UBound(a))

a(1) = "G1": b(1) = Evaluate(a(1) & "= ""H*"""): c(1) = RGB(255, 99, 99) 'red
a(2) = "n1": b(2) = Evaluate(a(2) & "= ""ps*"""): c(2) = RGB(99, 99, 255) 'Blue
a(3) = "n1": b(3) = Evaluate(a(3) & "= ""cs*"""): c(3) = RGB(99, 99, 255) 'Blue
a(4) = "ac1": b(4) = Evaluate(a(4) & "< ""1"""): c(4) = RGB(150, 150, 150) 'grey

For i = 1 To UBound(a)
Range(a(i)).Interior.Color = xlNone
If b(i) Then Range(a(i)).Interior.Color = c(i)
Next i
End Sub

[Kenneth Hobs]

There are many tutorials for Immediate Window. Here are some debugging tips by xld. http://www.vbaexpress.com/forum/show...ed-Debug-Print

You can attach a file if you like. Click Go Advanced button lower right of a reply, and Manage Attachments link below reply box. Browse and upload the file.

As I showed you, Evaluate(), can evaluate a formula and return the result. You can make your formulas manually as you would for conditional format which can be used as I demonstrated. Then those can be used in code.

You can also skip worksheet formulas and use methods like LIKE and INSTR(). Notices how LIKE can used a wild card character like ? or *. Note the quick results from the Immediate Window as shown in the code at the end. I typed the first line and the second line is the result.

Once you get something to work, you can add a WorkSheet Change event to act on cell values that are changed manually.

?asc("H") 72 
?asc("H*")
 72 
?"H1">"H"
True
?"h1">"H"
True
?"h">"H"
True
?asc("h")
 104 
?instr("Hi","H")
 1 
?instr("h1","H")
 0 
?"H1" like "H*"
True
?"h1" like "H*"
False

[ponderance]

First 100 lines are what comes in the file (with edited fields. 123 could be any characters)

second 100 lines are what formatting i apply with the conditional formatting.

sorry, i really can't grasp programming anymore and all the stuff you listed the last part of the email washed right off my brain. though now i understand the immediate window and will launch off on my own this weekend when i have free time to figure out work stuff like this.vtmp.xlsx

[ponderance]

Sorry. I really thought this would be easy. Especially with the conditional formatting code that works when I slap it in. Just, doing it 30 times a day is untenable