PDA

View Full Version : Color background detection in a cell



Hippo8
01-07-2013, 06:26 AM
Hi everyone, i'm facing some problems and i would like to ask for your help.

I have two columns with dates, where some of the dates are different.
Ex:

26-06-2009 16,07 26-06-2009
29-06-2009 16,22 29-06-2009
30-06-2009 16,09 30-06-2009
01-07-2009 16,24 01-07-2009
02-07-2009 16,04 02-07-2009
03-07-2009 16,03 06-07-2009
06-07-2009 16,04 07-07-2009
07-07-2009 16,01 08-07-2009
08-07-2009 15,92 09-07-2009

I need to search in all the file, for this situation an then copy the date in the left column to paste it in the right column. To stay like this:

26-06-2009 16,07 26-06-2009
29-06-2009 16,22 29-06-2009
30-06-2009 16,09 30-06-2009
01-07-2009 16,24 01-07-2009
02-07-2009 16,04 02-07-2009
03-07-2009 16,03 03-07-2009
06-07-2009 16,04 06-07-2009
07-07-2009 16,01 07-07-2009
08-07-2009 15,92 08-07-2009

With the conditional formatting i made all the different dates appear in red, which make the task more easy.

I'm now trying to automate the process with some vba. I'm trying to run the column A searching for a red background and then copy the date and insert as a new line in the other column.

Hope i express my self correctly.

If Range("A2:A1500").Interior.ColorIndex = -4142 Then

End If

I was trying some like this, there are a way so i know what the red background cell column and row are?

Then my objective is work with this recorded macro:
Range("C360:D360").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A360").Select
Selection.Copy
Range("C360").Select
ActiveSheet.Paste
Range("D359").Select
Application.CutCopyMode = False
Selection.Copy
Range("D360").Select
ActiveSheet.Paste

Adapting the column number automatically.

Thank You

Kenneth Hobs
01-07-2013, 07:06 AM
Colorindex is not used for conditional formatting. Use the condition criterion to find what you need.

p45cal
01-07-2013, 07:53 AM
FYI cross post:
http://www.mrexcel.com/forum/excel-questions/677814-color-background-detection-cell.html