Highlight Column VBA

B

bmkelly

Board Regular
Joined
Mar 26, 2020
Messages
172
Office Version
  1. 365
Platform
  1. Windows
Again thought this would be simple but run into an issue with this code

VBA Code: 
    'Highlighting Columns'
        Range("Table1[[#Headers],[Price]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent6"
        Range("Table1[[#Headers],[Analyst Notes]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent6"
        Range("Table1[[#Headers],[Transaction Date]:[Transaction Type]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent6"
        Range("Table1[[#Headers],[Analyst Notes 2]:[Transfer Notes]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent2"

This is part of my table below

Test Priors.xlsx
ADEFGPQSTUVWXYZAA
1Customer DifferenceModel DifferenceWarranty DifferenceVendor DifferenceCoverage DifferencePriceNotesAnalyst NotesAnalyst Notes 2Vendor Notes FRP/Historical Difference FRP and HistoricalCoverage NotesTransfer NotesTransaction DateTransaction Type
2NNYNYWarranty and Coveragedsnafjsdnf;Old to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
3NNYNYWarranty and Coveragesdfnodsncs;Old to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
4NNYNYWarranty and Coveragesdnjks;bkaOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
5NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
6NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
7NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
8NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
9NNNNYCoverageOld to $ 70.00FRP 70 HIST 0Old Labor Only - Missing Strategy to Labor Only - Vendor Warranty - Full ServiceTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS11/4/2021
Detail
Cell Formulas
RangeFormula
U2:U9U2=CONCATENATE("Old ",[@[IL_Vendor_Contract_Amount]]," to ",[@[Vendor_Contract_Amount]])
V2:V9V2=[@FRP]-[@[Historic Price]]
W2:W9W2=CONCATENATE("FRP ",[@FRP]," HIST ",[@[Historic Price]])
X2:X9X2=CONCATENATE("Old ",[@[IL_CoverageStrategy]]," to ",[@CoverageStrategy])
Y2:Y9Y2=CONCATENATE("Transfer from ",[@[IL_RSQM_Cust_Name]]," to ",[@[RSQM_Cust_Name]])


You can see that the highlight column for Price goes all the way down (because there isnt currently no data in the column) but the Analyst Notes, Transaction Date and Transaction Type stop highlighting after data is in the cell. I would like for the highlight columns to go to the very bottom of the table.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
bmkelly said:
Again thought this would be simple but run into an issue with this code

VBA Code: 
    'Highlighting Columns'
        Range("Table1[[#Headers],[Price]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent6"
        Range("Table1[[#Headers],[Analyst Notes]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent6"
        Range("Table1[[#Headers],[Transaction Date]:[Transaction Type]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent6"
        Range("Table1[[#Headers],[Analyst Notes 2]:[Transfer Notes]]").Select
        Range(Selection, Selection.End(xlDown)).Select
            Selection.Style = "40% - Accent2"

This is part of my table below

Test Priors.xlsx
ADEFGPQSTUVWXYZAA
1Customer DifferenceModel DifferenceWarranty DifferenceVendor DifferenceCoverage DifferencePriceNotesAnalyst NotesAnalyst Notes 2Vendor Notes FRP/Historical Difference FRP and HistoricalCoverage NotesTransfer NotesTransaction DateTransaction Type
2NNYNYWarranty and Coveragedsnafjsdnf;Old to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
3NNYNYWarranty and Coveragesdfnodsncs;Old to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
4NNYNYWarranty and Coveragesdnjks;bkaOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
5NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
6NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
7NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
8NNYNYWarranty and CoverageOld to $ 25.57FRP 55 HIST 29.43Old Labor Only - InHouse-Labor Only to Labor Only - Vendor Warranty - Repair OnlyTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS
9NNNNYCoverageOld to $ 70.00FRP 70 HIST 0Old Labor Only - Missing Strategy to Labor Only - Vendor Warranty - Full ServiceTransfer from UNC Hospitals-CTS to UNC Hospitals-CTS11/4/2021
Detail
Cell Formulas
RangeFormula
U2:U9U2=CONCATENATE("Old ",[@[IL_Vendor_Contract_Amount]]," to ",[@[Vendor_Contract_Amount]])
V2:V9V2=[@FRP]-[@[Historic Price]]
W2:W9W2=CONCATENATE("FRP ",[@FRP]," HIST ",[@[Historic Price]])
X2:X9X2=CONCATENATE("Old ",[@[IL_CoverageStrategy]]," to ",[@CoverageStrategy])
Y2:Y9Y2=CONCATENATE("Transfer from ",[@[IL_RSQM_Cust_Name]]," to ",[@[RSQM_Cust_Name]])


You can see that the highlight column for Price goes all the way down (because there isnt currently no data in the column) but the Analyst Notes, Transaction Date and Transaction Type stop highlighting after data is in the cell. I would like for the highlight columns to go to the very bottom of the table.
Click to expand...
I know theres is End(xlUp).Row which I could do by just selecting column P for Price etc but what I would like to accomplish is for it to search by column header that way if there are columns that get added it doesnt mess up the macro any
 
Upvote 0
maybe
VBA Code: 
    Dim oLo As ListObject
    Set oLo = Sheets("Detail").ListObjects("Table1")

'Highlighting Columns'
    Range("Table1[[#Headers],[Price]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent6"
    Range("Table1[[#Headers],[Analyst Notes]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent6"
    Range("Table1[[#Headers],[Transaction Date]:[Transaction Type]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent6"
    Range("Table1[[#Headers],[Analyst Notes 2]:[Transfer Notes]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent2"
 
Upvote 0
Solution
NoSparks said:
maybe
VBA Code: 
    Dim oLo As ListObject
    Set oLo = Sheets("Detail").ListObjects("Table1")

'Highlighting Columns'
    Range("Table1[[#Headers],[Price]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent6"
    Range("Table1[[#Headers],[Analyst Notes]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent6"
    Range("Table1[[#Headers],[Transaction Date]:[Transaction Type]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent6"
    Range("Table1[[#Headers],[Analyst Notes 2]:[Transfer Notes]]").Offset(1).Resize(oLo.ListRows.Count).Style = "40% - Accent2"
Click to expand...
Thank you again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
382
kcampbell675
K
B
  • Solved
macro to add text to existing text and not overwrite
Replies
4
Views
565
bmkelly
B
B
  • Question
Find Column Header and Format
Replies
2
Views
298
Fluff
Fluff
N
  • Question
Export from Excel to Access via VBA based on cell Value
Replies
0
Views
1K
NewPadawan
N

Forum statistics

Threads
1,216,308
Messages
6,129,993
Members
449,550
Latest member
LML2892

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top