Need to color specific cells on another sheet automatically

csteps

New Member
Joined
May 19, 2020
Messages
39
Office Version
365
Platform
Windows
Need the cells listed in yellow to color those cells on another sheet automatically
1590022749417.png
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
What column are these in on the current sheet ?
Which sheet does it have to change on ?
What color do they need to be ?

So, more information is required !!!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
Try
VBA Code:
Sub MM1()
Dim lr As Long, r As Long, str As String
lr = Cells(Rows.Count, "K").End(xlUp).Row
For r = 6 To lr
    str = Cells(r, "k").Value
    Sheets("Visual").Range(str).Interior.Color = vbRed
Next r
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
Just in case you have an invalid cell range
VBA Code:
Sub MM1()
Dim lr As Long, r As Long, str As String
lr = Cells(Rows.Count, "K").End(xlUp).Row
On Error GoTo err:
For r = 6 To lr
    str = Cells(r, "k").Value
    Sheets("Visual").Range(str).Interior.Color = vbRed
Next r
err:
MsgBox "Incorrect address @ " & Cells(r, "k").Address & " !!"
End Sub
 

csteps

New Member
Joined
May 19, 2020
Messages
39
Office Version
365
Platform
Windows
Thanks for the help,
The 1st VBA worked for the [ K6 ] but did not work for the rest in column k
The second gives the following error:
1590027996435.png
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
It works fine for me.....
Do you have a range "ALQ10:ALV10" on the Visual Sheet
OR check to make sure there are no spaces in that cell...leading / trailling or in between !
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
How did you arrive at the values in Col "K" ?
If a formula can you post the formula please !
 

csteps

New Member
Joined
May 19, 2020
Messages
39
Office Version
365
Platform
Windows
=CONCATENATE(E7,J7,I7)
This is for column K
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
Did you check all of those columns for unnecessary spaces?
Try changing the formula to
Rich (BB code):
=TRIM(CONCATENATE(E7,J7,I7))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,038
Messages
5,466,164
Members
406,471
Latest member
tsou88

This Week's Hot Topics

Top