Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Now in the third part (after Macro for Border and Macro for Border (Part 2)) I need to apply formating to more than one row. For example
Header 1 | Header 2 | Header 3 |
---|---|---|
XYZ | 10 | 20 |
ABC | 30 | 40 |
Total1 | 40 | 60 |
PQR | 30 | 40 |
Total2 | 10 | 20 |
Now my goal is to add the border for Header 2 and Header 3 which corresponds to the cell which says Total1 and Total2
I guess I can write a long code which will work, but is there a for loop I can use here to avoid writing a huge code?
Thanks,
Sunny
Update: Highlighted the text in red to show which cells need to be formatted
I think I got this one as well using a second loop
WhatToFind = Array("Total1", "Total2")
For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
For vOffset = 2 to 3
Set Rng = XLSheet.Cells.Find(WhatToFind(iCtr)).Offset(0, vOffset)
So I have handled this partially for each of the search word, now I need to get this for two columns
WhatToFind = Array("Total1", "Total2")
For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
Set Rng = XLSheet.Cells.Find(WhatToFind(iCtr)).Offset(0, 2)
I think I got this one as well using a second loop
WhatToFind = Array("Total1", "Total2")
For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
For vOffset = 2 to 3
Set Rng = XLSheet.Cells.Find(WhatToFind(iCtr)).Offset(0, vOffset)
Hi Sunny,
Again sorry for the delayed reponse. Here is the code.
Set objCells=XLSheet.Cells
With objCells
Set c = .Find ("Total*")
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set Rng = XLSheet.Range("B" & c.row & ":C" & c.row)
With Rng.Borders(8)
.LineStyle = 1
.Weight = 2
.ColorIndex = 0
.TintAndShade = 0
End With
With Rng.Borders(9)
.LineStyle = -4119
.ColorIndex = 0
.TintAndShade = 0
.Weight = 4
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With