0

My issue:

I want to loop through a range, and whenever it finds a coloured cell, it should copy the cell to the left to the cell to the right of it. And then afterwards paste it into an other worksheet.

My code loops through the cells easily, but doesn't copy the information, and paste it into the other sheet.

My Code:

Sub LoopForCondFormatCells()

Dim sht3, sht4 As Worksheet
Dim ColB, c As Range
Set sht3 = Sheets("Compare")
Set sht4 = Sheets("Print ready")

ColB1 = Range("G3:G86")
Set ColB = Range(ColB1)

For Each c In ColB.Cells
If c.Interior.Color = RGB(250, 191, 143) Then _
'Must be here, where the code is wrong. I actually don't need to look for .Count _
Since I need the code to find a cell, and if it is conditionally formatted _
, then do following steps.
        CValue = c.Address(False, False, xlA1)
        CValueOffsetL = Range(CValue).Offset(0, -1).Address(False, False, xlA1)
        CValueOffsetR = Range(CValue).Offset(0, 1).Address(False, False, xlA1)
        sht3.Range(CValueOffsetL, CValueOffsetR).Copy
        KvikOffIns = sht4.Range(HosKvikOff).Offset(0, -1).Address(False, False, xlA1)
        sht4.Activate
        sht4.Range(KvikOffIns).PasteSpecial xlPasteFormats
        End If
    Next c

End Sub

Goal:

I want the macro to loop through the cells, and find whatever cells, which has the color "RGB(250, 191, 143)". Whenever it comes across a cell, which is colored with "RGB(250,191,143)", it should do the steps, or at least a cell that is colored:

CValue = c.Address(False, False, xlA1)
CValueOffsetL = Range(CValue).Offset(0, -1).Address(False, False, xlA1)
CValueOffsetR = Range(CValue).Offset(0, 1).Address(False, False, xlA1)
sht3.Range(CValueOffsetL, CValueOffsetR).Copy
KvikOffIns = sht4.Range(HosKvikOff).Offset(0, -1).Address(False, False, xlA1)
sht4.Activate
sht4.Range(KvikOffIns).PasteSpecial xlPasteFormats

What should I write in my "If c Is" line to get the macro to do what I want it to do?

Patrick S
  • 325
  • 2
  • 12
  • Single line declarations like this Dim sht3, sht4 As Worksheet only the last object is a worksheet and the first is a variant. Dim sht3 As Worksheet, sht4 As Worksheet etc. – QHarr Jan 06 '18 at 08:13
  • why not say offset(0,1) = offset(0,-1) for the first bit? and union the range to copy? – QHarr Jan 06 '18 at 08:17
  • 1
    The key information is hidden in a comment: "find a cell, and if it is conditionally formatted". Colors of conditional formats are **not** `Interior.Color`. But `DisplayFormat.Interior.Color` or `FormatConditions(x).Interior.Color`. See https://stackoverflow.com/questions/45122782/how-to-get-the-background-color-from-a-conditional-formatting-in-excel-using-vba. – Axel Richter Jan 06 '18 at 08:17
  • 2
    I have never understood why checking for colors resulting from conditional formatting shall be necessary at all. Because one can simply checking for the same condition as of the conditional formatting instead of the resulting color. – Axel Richter Jan 06 '18 at 08:28
  • Oh Okay... When I think about it, it makes sense, since it conditionally FORMATTED, thank you! Would you say that I should check for the condition, which in this case is unique value? And if so, how should I?? – Patrick S Jan 06 '18 at 08:42
  • 1
    "check for the condition, which in this case is unique value? And if so, how should I": `If c.Value = 123 Then` if 123 is your unique value? But what do you think is the meaning of underscore after the `...Then _` in your initial code? – Axel Richter Jan 06 '18 at 08:52
  • Well, it compares two sets of data, whereas the unique values will nr colored “RGB(250,191,143)”. It has about 100 different values, and the unique values variates from time to time... – Patrick S Jan 06 '18 at 09:08
  • Well this is more complicated. Than either you need transferring the duplicate search to code or using one of the approaches linked in my first comment. – Axel Richter Jan 06 '18 at 09:24
  • So looking for color might the easiest way afterall?? :) – Patrick S Jan 06 '18 at 09:26
  • The the easiest way, maybe. The best way? Well, that depends... ;-). – Axel Richter Jan 06 '18 at 09:31
  • We’ll see if it works :) – Patrick S Jan 06 '18 at 10:08

0 Answers0