StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I like Tim's comment but also, look at what you are writing you have an extra <code>Cells</code> in there try it without the <code>Cells</code> and see if it works.</p> <pre><code>rngTo.Interior.ColorIndex = rngFrom.Interior.ColorIndex </code></pre> <p><strong>Update:</strong> The above code only works when the <code>colorindex</code> is the same value over the whole range otherwise it doesn't work.</p> <p><strong>Update 2:</strong></p> <p>This will do it for you. What was going on before is that <code>ColorIndex</code> doesn't hold an array, only as single value so if it had multiple values it would return a <code>Null</code> value. <code>Color</code> also doesn't hold multiple values so it returns white if it contains multiple values.</p> <pre><code>Private Sub ColorRange() 'Dim dicColors As Dictionary Dim dicColors As Object Dim dColor As Double Dim rCopy As Range, rPaste As Range, rNext As Range Dim wksCopy As Worksheet, wksPaste As Worksheet Dim vColor As Variant Set wksCopy = ActiveWorkbook.Worksheets("Sheet1") Set wksPaste = ActiveWorkbook.Worksheets("Sheet2") Set rCopy = wksCopy.UsedRange 'Set dicColors = New Dictionary Set dicColors = CreateObject("Scripting.Dictionary") 'Loop through entire range and get colors, place in dictionary. For Each rNext In rCopy dColor = rNext.Interior.Color If dicColors.Exists(dColor) Then Set dicColors(dColor) = Union(dicColors(dColor), wksPaste.Range(rNext.Address)) Else Set rPaste = wksPaste.Range(rNext.Address) dicColors.Add dColor, rPaste End If Next rNext 'Color the ranges For Each vColor In dicColors.Keys 'If color isn't white then color it, otherwise leave black, if the 'worksheet you are copying to has colors already then you should do an 'else statement to get rid of the coloring like this 'dicColors(vColor).Interior.ColorIndex = xlNone If vColor <> 16777215 Then dicColors(vColor).Interior.Color = vColor Next vColor End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload