CODEWITHSUNDEEP
vbaexcel
James Wilson
James Wilson

Reputation: 21

EXCEL VBA change selected cells to colour

I am trying to write a peice of VB that will look at a run an iff statement and then if true change the colour of the row that was true. Currently I can get it only to change the colour for one cell.

the VB looks like this.

Sub Blue()

Set A = Sheets("Analysis")

Dim d
Dim j
d = 1
j = 1

Do Until IsEmpty(A.Range("B" & j))


If (A.Range("F" & j) <> A.Range("G" & j)) Or (A.Range("H" & j) <> A.Range("I" & j)) Then

d = d + 1
A.Range("B" & j).Interior.ColorIndex = 28

End If

j = j + 1
Loop

End Sub

That works. When i try to change this to highlight a selection of cells in the table. i get a 1004 run time error Sub Blue()

Set A = Sheets("Analysis")

Dim d
Dim j
d = 1
j = 1

Do Until IsEmpty(A.Range("B" & j))


If (A.Range("F" & j) <> A.Range("G" & j)) Or (A.Range("H" & j) <> A.Range("I" & j)) Then

d = d + 1
A.Range("A:U" & j).Interior.ColorIndex = 28

End If

j = j + 1
Loop

End Sub

I have spent loads of time looking on the internet and cant find any answeres to why this does not work. any help would be appreicated.

I am using VB as this will be part of a much larget peice of VB.

Upvotes: 2

Views: 2001

Answers (3)

Jorgen Unger
Jorgen Unger

Reputation: 11

A range for more then one cell should be in this format "A1:U1", your code is equal to "A:U1".

You could code like this:

A.Range("A" & j & ":U" & j).Interior.ColorIndex = 28

I prefer this code:

A.Range(Cells(j, 1), Cells(j, 21)).Interior.ColorIndex = 28

Good Luck

Jorgen Unger

Upvotes: 1

user3598756
user3598756

Reputation: 29421

I'm not by any PC to test it but you can try this

A.Range("A:U").rows(j).Interior.ColorIndex = 28

Upvotes: 1

Dawid SA Tokyo
Dawid SA Tokyo

Reputation: 376

To change colour of columns A to U use this:
A.Range("A" & j & ":U" & j).Interior.ColorIndex = 28 - in your line you are missing the row for column A.

To change colour of the entire row use this:
A.Range("B" & j).EntireRow.Interior.ColorIndex = 28

Upvotes: 1

Related Questions