0

I've been learning VBA and have a moderate grasp of some concepts, however I'm still learning so keep it simple.

I am having trouble getting my code to recognise the interior cell colour and to manage the coloured cells how I desire.

Below is my code.

'highlight duplicate data'
Windows("name of workbook").Activate

Sheets("name of sheet").Activate
Columns("C:C").Select
   Selection.FormatConditions.AddUniqueValues
   Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   Selection.FormatConditions(1).DupeUnique = xlDuplicate

     With Selection.FormatConditions(1).Font
        .Color = 1
        Selection.FormatConditions(1).Interior.ColorIndex = 3
     End With

'So far all is working well and the cells turn red'

'consolidate red cells codes'
Dim sampi As Integer

  With ActiveSheet
    refcells = .Cells(.Rows.Count, 3).End(xlUp).Row
  End With

For sampi = 6 'my data starts from here'  To refcells

Cells(sampi, 3).Activate

If Cells(sampi, 3).Interior.ColorIndex = 3 Then redcells = Cells(sampi, 3) Else GoTo nextsampi 'This always comes out as false'

Cells.Find(What:=redcells, After:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select

Selection.Copy

Range("a1001").PasteSpecial



nextsampi:

    Next sampi


Call nextsub(1)

All my ranges are defined as they should be and the active cell moves as I expected, but the 'if' statement is never true despite red cells being visible in column C. I have enabled screen updating and everything else I can think of.

any suggestions are appreciated.

Jess

Jess
  • 1
  • Thanks Tim Williams. – Jess Jul 16 '17 at 23:54
  • Is anyone able to confirm that "Cells(x, x).FormatConditions(1).Interior.Color = 255" will only check if conditional formatting has been applied to the cell and not that the cell has been changed based on the conditional format? – Jess Jul 17 '17 at 01:22
  • do `debug.print Cells(x, x).FormatConditions(1).Interior.Color` and then change cell value so that conditional format changes color and do debug.print again – jsotola Aug 06 '17 at 16:48

0 Answers0