Offset range

[ApolloID]

Hi, i need to highlight a range (A:K) from active cell.
And i need a macro to do this from 3 to 3 rows.

If active cell is A4, i need the macro to highlight rows 4,7,10,13...etc, and to highlight from A to K.

Can this be done?

Thanks!

 

[Ralajer]

Try this

Sub HiglightFromActiveCell()
    Dim lastRow As Long
    Dim i As Long
    Application.ScreenUpdating = False

[COLOR="Red"]    '// Last Row containing data
'    lastRow = Range("A:K").Find("*", , , , xlByRows, xlPrevious).Row
    '// All Rows (can take a while especially on Excel 2007 +)
'    lastRow = Rows.Count[/COLOR]
    '// Specific Row number
    lastRow = 100
    If ActiveCell.Row > lastRow Then GoTo EarlyExit
    For i = ActiveCell.Row To lastRow Step 3
        '// Fill color yellow = 65535
        Range(Cells(i, "A"), Cells(i, "K")).Interior.Color = 65535
    Next i
EarlyExit:
    Application.ScreenUpdating = True
End Sub

Wasn't sure where you wanted to end the highlighting so I include the three options.

 

[ApolloID]

Hi,

It's perfect!!!!!!!

I only need to make the font color white and bold and to add "bold" border.

Can this be done?

Thanks!

 

[Norie]

If that's all you want to do, why select?

This will do the formatting for rows where there is data in column A.

Dim rng As Range
 
    Set rng = ActiveCell
 
    While rng.Value <> ""
 
        With rng.Resize(, 10)
 
            .Font.Color = vbWhite
            .Font.Bold = True
            .BorderAround xlContinuous, xlMedium, xlAutomatic
        End With

        Set rng = rng.Offset(3)
 
    Wend

 

[ApolloID]

Hi, Norie,

I don't want to format all rows. Only from three to three rows. And from A to K.

Thanks!

 

[Norie]

What do you mean by 3 to 3?

The code I posted will apply the formatting to columns A:K in every 3rd row, starting at the row of the active cell.

In my test it formatted A4:K4, A7:K7, A10:K10 and so on.

Sorry if that's not what's needed.

 

[ApolloID]

My mistake.
Sorry Norie...i have alot on my mind.
You are correct ofcourse.
I didn't checked your formula. I only looked at it for a moment but i didn't saw the part with offset.

Sorry again, ...

Anyway, the problem is that A column doesn't allways have content in each cell.

So, i need the formula to work even if there is nothing in A column.

Thanks.

 

[Norie]

So how many rows should the code go down?

 

[ApolloID]

6380

 

[ApolloID]

I know it's going to take a long time to run, but it will be easier than if i do it myself.
Thanks.