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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
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,872
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,872
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,872
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,872
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,872
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,098,915
Messages
5,465,423
Members
406,427
Latest member
gboomer

This Week's Hot Topics

Top