Click here for important information about Computing.net.

Solved VBA code help for corresponding cell colors needed

Tags:
command prompt
September 29, 2014 at 16:33:30
Specs: Windows 7
I am looking for help with VBA code in excel. I am no VBA professional my any means but I think what I need shouldn't be too complex. I am trying to create a spreadsheet that will allow a user to fill color in numbered cells in one group of cells and then use a command button to automatically fill the same color to corresponding cells in a group below. I had come across the post below and that was exciting because it looks like it can be done but this code will not work for the way my numbered cells are laid out in the worksheet. I would love to send the file if someone is willing to take a look and help. Thanks!! Heidi

DerbyDad03 had posted this at http://www.computing.net/answers/of...

"Run this and each cell in C12:AF17 will be filled with the same colors as C5:AF10 on a cell by cell basis.

In other words, for each cell in C5:AF10, the cell 7 rows below it in the same column will be filled with the color of that cell.

Sub MatchColors()
For Each myCellColor In Range("C5:AF10")
myCellColor.Offset(7, 0).Interior.ColorIndex = _
myCellColor.Interior.ColorIndex
Next
End Sub"


See More: VBA code help for corresponding cell colors needed

Report •

#1
September 30, 2014 at 04:03:19
Can you describe how your cells are laid out and what it is you are trying to do?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 30, 2014 at 11:48:35
Hopefully this works. Trying to post an image of the cell layout. If the picture does not show it should be viewable here: http://postimg.org/image/dmpg1zps1/

[url=http://postimg.org/image/dmpg1zps1/][img]http://s24.postimg.org/dmpg1zps1/Capture.jpg[/img][/url]


Report •

#3
September 30, 2014 at 19:50:04
✔ Best Answer
The comments in this code should explain how it works.

Sub ColorMyWorld()
 'Clear fill color in lower range
   Sheets(1).Range("C14:L21").Interior.ColorIndex = xlNone
 'Loop through cells in upper range looking for cells with fill color
    For Each myCell In Sheets(1).Range("C4:W12")
     If myCell.Interior.ColorIndex <> xlNone Then
 'If cell has fill color, find value in lower range and match fill color
         With Sheets(1).Range("C14:L21")
           Set c = .Find(myCell.Value, lookat:=xlWhole, LookIn:=xlValues)
            If Not c Is Nothing Then
             c.Interior.ColorIndex = myCell.Interior.ColorIndex
            End If
         End With
     End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions
See More ↓

#4
October 1, 2014 at 09:17:05
Amazing, Thank you so much!!!

Report •
Start a discussion

Ask Question