Copy ONLY cell color

[Antonf]

I need to copy only the color of certain cells to other ranges, not the number formats, borders, etc. If the "standard" Excel palette colors were used it would have been simple, but that's not the case.

Thanks in advance!

 

[Trevor G]

Copy your range of cells and then use paste special and you should find formats there which will give you what you want based on your question, if there is more to this then you will have to give clear questions.

Trevor

 

[Antonf]

When I use Paste Special, Format "other" formats such as the font, alignment, etc are also applied to the range. I need ONLY the color to be the same, nothing else.

I hope this is clearer now!

 

[Trevor G]

What I have done here is created a couple of macros to see if this can advance your request

First here is the colour pallete index options which show the main colours and there equivalent numbers

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #000000"> </TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ff0000"> </TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #00ff00"> </TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #0000ff"> </TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #ff00ff"> </TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BACKGROUND-COLOR: #800000"> </TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BACKGROUND-COLOR: #008000"> </TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BACKGROUND-COLOR: #000080"> </TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="BACKGROUND-COLOR: #808000"> </TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="BACKGROUND-COLOR: #800080"> </TD><TD style="TEXT-ALIGN: right">13</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="BACKGROUND-COLOR: #008080"> </TD><TD style="TEXT-ALIGN: right">14</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="BACKGROUND-COLOR: #808080"> </TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BACKGROUND-COLOR: #9999ff"> </TD><TD style="TEXT-ALIGN: right">17</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="BACKGROUND-COLOR: #993366"> </TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="BACKGROUND-COLOR: #ffffcc"> </TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="BACKGROUND-COLOR: #660066"> </TD><TD style="TEXT-ALIGN: right">21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="BACKGROUND-COLOR: #ff8080"> </TD><TD style="TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="BACKGROUND-COLOR: #0066cc"> </TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="TEXT-ALIGN: right">24</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="BACKGROUND-COLOR: #000080"> </TD><TD style="TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="BACKGROUND-COLOR: #ff00ff"> </TD><TD style="TEXT-ALIGN: right">26</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="TEXT-ALIGN: right">27</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="BACKGROUND-COLOR: #00ffff"> </TD><TD style="TEXT-ALIGN: right">28</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="BACKGROUND-COLOR: #800080"> </TD><TD style="TEXT-ALIGN: right">29</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="BACKGROUND-COLOR: #800000"> </TD><TD style="TEXT-ALIGN: right">30</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="BACKGROUND-COLOR: #008080"> </TD><TD style="TEXT-ALIGN: right">31</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="BACKGROUND-COLOR: #0000ff"> </TD><TD style="TEXT-ALIGN: right">32</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD style="BACKGROUND-COLOR: #00ccff"> </TD><TD style="TEXT-ALIGN: right">33</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD><TD style="TEXT-ALIGN: right">34</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD style="BACKGROUND-COLOR: #ccffcc"> </TD><TD style="TEXT-ALIGN: right">35</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD style="BACKGROUND-COLOR: #ffff99"> </TD><TD style="TEXT-ALIGN: right">36</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD style="BACKGROUND-COLOR: #99ccff"> </TD><TD style="TEXT-ALIGN: right">37</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD style="BACKGROUND-COLOR: #ff99cc"> </TD><TD style="TEXT-ALIGN: right">38</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD style="BACKGROUND-COLOR: #cc99ff"> </TD><TD style="TEXT-ALIGN: right">39</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD style="BACKGROUND-COLOR: #ffcc99"> </TD><TD style="TEXT-ALIGN: right">40</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD style="BACKGROUND-COLOR: #3366ff"> </TD><TD style="TEXT-ALIGN: right">41</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD style="BACKGROUND-COLOR: #33cccc"> </TD><TD style="TEXT-ALIGN: right">42</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</TD><TD style="BACKGROUND-COLOR: #99cc00"> </TD><TD style="TEXT-ALIGN: right">43</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</TD><TD style="BACKGROUND-COLOR: #ffcc00"> </TD><TD style="TEXT-ALIGN: right">44</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</TD><TD style="BACKGROUND-COLOR: #ff9900"> </TD><TD style="TEXT-ALIGN: right">45</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">46</TD><TD style="BACKGROUND-COLOR: #ff6600"> </TD><TD style="TEXT-ALIGN: right">46</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">47</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="TEXT-ALIGN: right">47</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">48</TD><TD style="BACKGROUND-COLOR: #969696"> </TD><TD style="TEXT-ALIGN: right">48</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">49</TD><TD style="BACKGROUND-COLOR: #003366"> </TD><TD style="TEXT-ALIGN: right">49</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">50</TD><TD style="BACKGROUND-COLOR: #339966"> </TD><TD style="TEXT-ALIGN: right">50</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">51</TD><TD style="BACKGROUND-COLOR: #003300"> </TD><TD style="TEXT-ALIGN: right">51</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">52</TD><TD style="BACKGROUND-COLOR: #333300"> </TD><TD style="TEXT-ALIGN: right">52</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">53</TD><TD style="BACKGROUND-COLOR: #993300"> </TD><TD style="TEXT-ALIGN: right">53</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">54</TD><TD style="BACKGROUND-COLOR: #993366"> </TD><TD style="TEXT-ALIGN: right">54</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">55</TD><TD style="BACKGROUND-COLOR: #333399"> </TD><TD style="TEXT-ALIGN: right">55</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">56</TD><TD style="BACKGROUND-COLOR: #333333"> </TD><TD style="TEXT-ALIGN: right">56</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Next is the macro which I used
Public Sub ColorTable()
Dim i As Integer
For i = 1 To 56
Range("A" & i).Interior.ColorIndex = i
Range("B" & i).Value = i
Next i
End Sub

Having done this I have then used a simple macro to request the index number for a particular cell via an inputbox

Sub color1()
Dim i As Integer
i = InputBox("Enter the colour number")
Range("h1").Interior.ColorIndex = i

End Sub

I hope this can help. Perhaps record a macro that will help find the colour index number and see if this can be adapted to activecell or range

Trevor

 

[Antonf]

Thanks - got it sorted out!