0

I am trying to highlight an entire column using vba code similar to:

Columns("A:A").select

but I would like to replace the "A" with a variable I which contains the numeric value of the columns, so A=1, B=2, etc.

Another issue is that on my sheet I also have lots of cells that are merged. so if I use a code like:

I=2
Columns(i).select

then it also highlight all columns of a merged cell that fall under the same column. I would like it so it just highlights ONE COLUMN, column I (variable). Something like a person just simply clicking the column to highlight entire selected column in excel sheet.

The code doesn't have to be "Columns" code, it was just an example and the one I know that works the way I kinda need it to be.

Thanks

0m3r
  • 12,286
  • 15
  • 35
  • 71
HumanlyRespectable
  • 203
  • 5
  • 12
  • 26
  • 1
    Merged cells are evil, don't use them! There is an alternative to merged cells: [Center Across Selection](https://accessanalytic.com.au/stop-merging-cells/). Otherwise you will always run into such issues with VBA. – Pᴇʜ Oct 25 '18 at 13:32
  • 2
    Avoid selection in any case; what is it you want to do with the cells in the column? You could do "it" without selecting the column. – Andy G Oct 25 '18 at 13:34
  • @AndyG there is a month and day (Example: "Oct-25") displayed on row 4 and across the columns displays the days. I want it so that when I open up the work sheet, it will highlight the entire column of today so it's easier to see where I am at. – HumanlyRespectable Oct 25 '18 at 13:58
  • 1
    If it is a simple timetable then do you *need* merged cells, compared to `center across selection`? Alternatively, you could settle for just selecting the first useful cell in the column on opening the workbook. – Andy G Oct 25 '18 at 14:12
  • @AndyG I already programmed an entire scheduler so that each job we have, we create job blocks using a form which turns unused cells to merged cells, which indicates how long a job will take. Basically, it's too late to get rid of merge cells unless I reprogram it all. (there's more to it than I am explaining). But I will probably just highlight the cells date as you suggested instead. I typically program in other languages such as python, c++ and C# so I had no idea how to get around this merged cell problem. Thanks. If you add this as an answer I can give you the checkmark. – HumanlyRespectable Oct 25 '18 at 14:27
  • 1
    @HumanlyRespectable You could use conditional formatting to highlight the column of today with eg background color or a border by checking the column's date against today's date: condition `=A$5=TODAY()` where A5 contains the date of the column A. This way today's column would be automatically colored if you open the workbook. Hard to explain that better without seeing your sheet. – Pᴇʜ Oct 25 '18 at 14:33
  • That's a good idea. You could apply the conditional formatting just to cells within the scheduler rather than entire columns. Or, as suggested, just to the header cell. – Andy G Oct 25 '18 at 14:35
  • @Pᴇʜ That is a very good idea, I might do that instead. Thanks. – HumanlyRespectable Oct 25 '18 at 16:02

2 Answers2

1

Merged cells are evil, don't use them! There is an alternative to merged cells: Center Across Selection. Otherwise you will always run into such issues with VBA.

Anyway is is a good idea to avoid using Select in Excel VBA.

For "highlighting" the column eg. change the background color.

Columns(5).Interior.Color = vbRed

This will color only column 5 except the merged cells.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 2
    Merged cells are not 100% evil. I used to think this way, but have since broadened my perspective. Sometimes they can be very useful and as long as you know they are there and why they are there, you can work around them. For example, I use them mainly for presentation purposes only and rarely code around them. That said, I developed a solution coding around them that turned out to be quite elegant in the final design (the coding maybe not so elegant :), but I did manage to functionalize that too!) – Scott Holtzman Oct 25 '18 at 13:49
  • @ScottHoltzman Hm, I think I can agree with that. But you still have to be very careful with them. If anyone introduces a merged cell that you didn't think about while writing the code it can easily drive you nuts. Especially when you wrote a code, tested it and it worked and then some user uses it and has merged cells it burns everything down. So I can agree that 100% evil was a bit too strong. – Pᴇʜ Oct 25 '18 at 14:13
  • 90% evil. I suppose a *toggle* method to call, and unmerge/remerge all merged cells, might be an option. We'd have to store a list of the ranges (or give them range names). – Andy G Oct 25 '18 at 14:15
  • @AndyG All that effort just for the "fun" to select a column? Good luck ;) There are probably easier ways to succeed. – Pᴇʜ Oct 25 '18 at 14:17
  • @Pᴇʜ I'm not planning to do it. Just that if someone insisted on including merged cells, and some detailed manipulation was to take place, then such a toggle could be a viable option... for someone (not me) – Andy G Oct 25 '18 at 14:22
  • @Pᴇʜ - Yes. That is where program design and architecture become very important skills to develop. The best design does not give the user the *chance to introduce things* that can break the code. – Scott Holtzman Oct 25 '18 at 15:39
0

if you want to color the used cells in a given column except merged ones:

Dim i As Long

i = 4
Dim cell As Range
For Each cell In Intersect(Columns(i), ActiveSheet.UsedRange)
    If Not cell.MergeCells Then cell.Interior.Color = vbGreen
Next
DisplayName
  • 13,283
  • 2
  • 11
  • 19