VBA code for searching Column from first row and formating the column

[rajltd]

I am coding a Excel macro in VB for searching a particular column (treat as header) from a number of worksheets in the workbook. That particular column Eg. Accessories, can be in more than one worksheet of the workbook, but it is always in the 3rd column of that particular worksheet (if there) of the workbook. The reason being I want to format the column size from 10 to 50 for that particular column.

Ans:

for each ws in worksheets
with ws.cells(1,3)
if .value = "Accessories" then
.entirecolumn.columnwidth=70
end if
end with
next

I received the above code from the forum.

Can I do the same thing by searching any cell (Not just Accessories in 3rd column, but say accessories in 5th column and other attributes in other column and different worksheets) from the first row.

Any help is very much appreciated.

Thanks

 

[SkipVought]

Hi,

for each ws in worksheets
  with ws.usedrange 
    for each h in range(ws.cells(1,1), cells(1, .columns.count + .column - 1))
      if h.value = "Accessories" then
        h.entirecolumn.columnwidth=70
      end if
    next 
  end with
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

oops...

for each ws in worksheets
  with ws.usedrange 
    for each h in range(ws.cells(1,1), ws.cells(1, .columns.count + .column - 1))
      if h.value = "Accessories" then
        h.entirecolumn.columnwidth=70
      end if
    next 
  end with
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com