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
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
=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,
Just traded in my OLD subtlety...
for a NUance!
RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column
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 SubSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column
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
RE: VB Code - Entire Row Colors for Table depending on Partial Data in Column
Thanks