Macro - Highlight range

[ApolloID]

Hi, i need a macro to highlight a range (A30:R1000), but not all rows. I need to highlight the first, then the third, then fifth,...etc. One row highlighted, the next to remain white, then highlighted, then to remain white...

Can this be done?
Thanks!

 

[Norie]

You can do it without code.

Select all the cells, you can just type the address in the name box to do that.

Goto conditonal formatting, chose format based on the result of a formula and use this formula

=MOD(ROW()+1,2)

 

[ApolloID]

Hi, it,s working! Thanks!
This code can work with the last filled cell?
To do the same thing but the range to be A30:last filled cell in row 29.

Can this be done?

Thanks!

 

[Norie]

It's not code.

Can't you just select the range you want?

You can use END + Down Arrow to goto the last populated cell, and the same with Right arrow for column.

There's also a keyboard shortcut to select the used range, and I'm sure there are others that can be used.

If you really want code I can post some.

 

[ApolloID]

The idea is that A30:R1000 is the table with infi, at this moment. But i will add info weekly, so range will became A30:T1000, then A30:V1000.

So, i need to highlight the range from colum A till last fill column. And the head table will be in row 29, so i need the code to look row 29 to see the last column filled.

Thanks!

 

[ApolloID]

Or, if its more simple, can the code hightlight all rows based on two conditions?
from A30 till the last filled cell of A column
and till the last filled cell in row 29.

Thanks!

 

[Norie]

It probably would be easier to just select the range and apply the conditional formattin.

In fact you might not even need to do that - you could just adjust the range.

Anyway, here's code that will apply the conditional formatting.

Option Explicit
 
Sub AlternateRows()
Dim rng As Range
 
    Set rng = Range("A30")
    Set rng = Range(rng, rng.End(xlToRight).End(xlDown))

    With rng.FormatConditions

        .Delete

        .Add xlExpression, , Formula1:="=MOD(ROW()+1,2)"

        .Item(1).Interior.ColorIndex = 3

    End With
 
End Sub

If that isn't what you want/need post back.

 

[ApolloID]

It's highlighting from A30 till the last filled cell in column, but not to the right. it's highlighting only columns A and B.
My table, now(test), contains datas from A30 till R75, but the code highlight from A30 till B75.

Not all cells in my table is filled. The only column etirely filled is column B, and the only row entirely filled is row 29.

Thanks!

 

[ApolloID]

Hi, it's working!!!

I had an empty cell in row 29.

Thanks alot!