×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VB Code - Entire Row Colors for Table depending on Partial Data in Column

VB Code - Entire Row Colors for Table depending on Partial Data in Column

VB Code - Entire Row Colors for Table depending on Partial Data in Column

(OP)
I tried man. I really did, but I can only go so long making no progress.... To make it worse, I thought of something else I need to do and cant remember what it was now. lol

Column "Number" or "ROB#" will be filled with a date similar to...
AA010_R01_B05
The only part of this data I am interested in right this moment is the R01. It might be R01, R02, R03, R04, R05 or R06.

I need the color for the entire row of data in the table to change according to which R0# it is.

I was finding articles on using VB to change rows according to column data, but nothing on using a formula to find a specific text inside a string. Or any formula for that matter. Which would have helped if I found that much.

The last VB code I tried to manipulate looks like this.....



Dim rng As Range
Dim i As Integer
Application.ScreenUpdating = False
Set rng = Range("a2:az1250")
i = 2
Do Until i = 10
With rng
If Cells(i, 6).Value = "=ISNUMBER(SEARCH("R01",[@Number]))" Then
Cells(i, 6).Interior.ColorIndex = 36
ElseIf Cells(i, 6).Value = "=ISNUMBER(SEARCH("R02",[@Number]))" Then
Cells(i, 6).Interior.ColorIndex = 40
ElseIf Cells(i, 6).Value = "=ISNUMBER(SEARCH("R03",[@Number]))" Then
Cells(i, 6).Interior.ColorIndex = 38
ElseIf Cells(i, 6).Value = "=ISNUMBER(SEARCH("R04",[@Number]))" Then
Cells(i, 6).Interior.ColorIndex = 35
ElseIf Cells(i, 6).Value = "=ISNUMBER(SEARCH("R05",[@Number]))" Then
Cells(i, 6).Interior.ColorIndex = 24
ElseIf Cells(i, 6).Value = "=ISNUMBER(SEARCH("R05",[@Number]))" Then
Cells(i, 6).Interior.ColorIndex = 15
Else
Cells(i, 6).Interior.ColorIndex = 2
End If
End With
i = i + 1
Loop
Application.ScreenUpdating = True


But I think I got this all screwed up or something. lol It is times like this I realize just how little I understand of VB code. lol Im decent with formulas but I suck at code. lol

RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column

Try a Conditional Format where

=FIND(C2,"_R01_")>0
...and apply the appropriate shade.

You'll need a spec for each color.

I think that applying to the first data row may propagate to all Structured Table rows.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column

Here's a VBA procedure. I loop thru the Number column and then the ROB # column. Had to turn off the ELSE on the second time through, or else all the rows shaded in Number were unshaded.

CODE

Sub ColorFormatRow()
    Dim rng As Range, r As Range, i As Integer
    
    Application.ScreenUpdating = False
    
    For i = 0 To 1
        Select Case i
            Case 0
                Set rng = Rows(1).Find("Number").Offset(1)
            Case 1
                Set rng = Rows(1).Find("ROB #").Offset(1)
        End Select
        
        Set rng = Range(rng, rng.End(xlDown))
        
        For Each r In rng
            If r.Value Like "*_R01_*" Then
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 36
            ElseIf r.Value Like "*_R02_*" Then
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 40
            ElseIf r.Value Like "*_R03_*" Then
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 38
            ElseIf r.Value Like "*_R04_*" Then
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 35
            ElseIf r.Value Like "*_R05_*" Then
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 24
            ElseIf r.Value Like "*_R06_*" Then
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 15
            Else
                If i = 0 Then _
                Intersect(r.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = 2
            End If
        Next
    Next
    
    Application.ScreenUpdating = True
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column

(OP)
Conditional formatting, at least to my understanding, isnt good for this situation because it wont be there the next file they give me.

I am creating this macro so that every time they give me or someone else a new file to use, which is often, we wont have to format it to how we can use it. I can just import the macro and run it and ts done automatically. Everyone who does this job gets used to the same set up every time. Others who do this wont know how to do conditional formatting. I had to teach one person how to use the filters even.

So i need to set the conditional formatting with the VB code I believe. ....... I believe. Doesnt mean I know. lol

RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column

(OP)
Crap, I replied and didnt see this last one. I will look at it in the morning. Thanks

RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column

(OP)
Code worked perfect. It had me confused for a bit but it was on me for not placing it in a good place within my code. lol

Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close