code color cells

[tuktuk]

Hey there,

I have a code that updates data from one file to another.....esentially, i run this code daily and it updates to a compiled daily log of all of the previous days data.

Currently, i am manually coloring the cells to visually assist in grouping what data has been uploaded for each day.

Can anyone help with adding this step to the code below......

Sub UpdatePOQntyReducDB()
Dim lookupfilename As String

lookupfilename = "\\Sr\SharedDocs\CSPSharedFILES\POQuantityTrackingTool\POQuantityTrackingReport.xls"

Workbooks.Open lookupfilename
Workbooks("POQuantityTrackingReport.xls").Activate

'locate the cell in first column +1 cell past the previous days upload
'this preps for the next upload
Dim LastRowDB As Long
LastRowDB = Cells(Rows.Count, "D").End(xlUp).Row
Range("A" & LastRowDB).Select

'move the actice cell down ONE ROW
ActiveCell.Offset(1, 0).Select


'Open the Current Days Invoices
Workbooks("POQntyReductionTool3.xls").Activate

Dim LastRowPO As Long

'Copy CurrentDays Invoices
LastRowPO = Cells(Rows.Count, "D").End(xlUp).Row
Range("A2:M" & LastRowPO).Select
Selection.Copy

'Open the CompilingMaster tab (database) file
Workbooks("POQuantityTrackingReport.xls").Activate
ActiveSheet.Paste
MsgBox ("Today's invoices have been updated to the POQuantiyTrackingReport.")

Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks("POQntyReductionTool3.xls").Activate
Range("A1").Select
Workbooks.Close

End Sub

HERE ARE THE TWO COLORS THAT I'D LIKE TO OSSOCILATE BETWEEN:

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With

and

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With

THANKS
Tuktuk

 

[Oaktree]

To alternate colors, use something like:

dim my_cell as range
for each my_cell in Range("A1:A"& LastRowDB)
if (my_cell.row mod 2)=1 then 
my_cell.interior.colorindex= 5 
else my_cell.interior.colorindex= 3
end if
next my_cell

 

[tuktuk]

i guess i am confused on the

if (my_cell.row mod 2)=1 then

thansk for your help.....


just to clarify....the file that the data is updating to is the only file to have this coloring sheme....it need to recognize the color above where it is posting to and then paste the current days range of data and then color that newly pasted data the other color.

where would i use your code in the code i pasted.

tuk

 

[Oaktree]

I may be misinterpreting your question.

The code I suggested will color even numbered rows one color and odd numbered rows another.

The mod (modulus) function essentially calculates the remainder when dividing by the divisor (2 in this case). If the remainder is 1 (i.e. an odd number), it chooses one color and if the remainder is 0, it will choose the other.

If that's what you're looking for, you can specify the range you're applying the alternate formatting with in the

Range("A1:A"& LastRowDB)

line.

 

[tuktuk]

we getting closer!!!!

i am really trying to just color code everytime new data is pasted to a 2nd workbook. the 2nd workbook will have compiled data from x amounts of previous pastes.

is there a way to "grab/remember" the color of a cell in my code

Range("A" & LastRowDB).Select

then i prep to paste the soon to be added data. that is i go to a new row....then copy the data from the first sheet, go back to the second, and paste that data.

at this point after

ActiveSheet.Paste

the range that is needed to be color is active.......hwo can i add the other NON - "grabbed/remember" color to this range.

does that help clarify?
[/code]

 

[Oaktree]

is there a way to "grab/remember" the color of a cell in my code

Sure.

my_color = Range("A" & LastRowDB).interior.colorindex

or similar

Then, you can use something like:

if my_color = 3 then 
Range(some range).interior.colorindex = 5
else Range(some range).interior.colorindex = 3
end if

 

[tuktuk]

my fundamentals are weak......i am running in to object not set errors and variable errors but can't figure it out......here is my attempt

Sub UpdatePOQntyReducDB()
Dim lookupfilename As String

lookupfilename = "\\Sr\SharedDocs\CSPSharedFILES\POQuantityTrackingTool\POQuantityTrackingReport.xls"

Workbooks.Open lookupfilename
Workbooks("POQuantityTrackingReport.xls").Activate

'locate the cell in first column +1 cell past the previous days upload
'this preps for the next upload
Dim LastRowDB As Long
LastRowDB = Cells(Rows.Count, "D").End(xlUp).Row
Range("A" & LastRowDB).Select


Dim my_color As ColorFormat
Set my_color = ActiveCell.Interior.ColorIndex
my_color = Range("A" & LastRowDB).Interior.ColorIndex

move the actice cell down ONE ROW
ActiveCell.Offset(1, 0).Select


'Open the Current Days Invoices
Workbooks("POQntyReductionTool3.xls").Activate

Dim LastRowPO As Long

'Copy CurrentDays Invoices
LastRowPO = Cells(Rows.Count, "D").End(xlUp).Row
Range("A2:M" & LastRowPO).Select
Selection.Copy

'Open the CompilingMaster tab (database) file
Workbooks("POQuantityTrackingReport.xls").Activate
ActiveSheet.Paste

'in TESTING PHASE
If my_color = 3 Then
Range("A2:M" & LastRowPO).Interior.ColorIndex = 5
Else: Range("A2:M" & LastRowPO).Interior.ColorIndex = 3
End If


MsgBox ("Today's invoices have been updated to the POQuantiyTrackingReport.")

Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks("POQntyReductionTool3.xls").Activate
Range("A1").Select
Workbooks.Close

End Sub

am i on the right track

 

[Oaktree]

Colorindex is a number, so you should

Dim my_color As integer

not Colorformat.

 

[tuktuk]

well now i am not getting errors anylonger and the data is being pasted correctly BUT

it is NOT coloring the cells.......as i step through the code though it is "grabbing" the previous data pasted's color.

might it have something to do with how the range is defined?

 

[Oaktree]

What value is the color index of the cell it's grabbing?

You will need to adjust the "3" and "5" values in

If my_color = 3 Then 
Range("A2:M" & LastRowPO).Interior.ColorIndex = 5 
Else: Range("A2:M" & LastRowPO).Interior.ColorIndex = 3 
End If

accordingly to your specific use...

Or, delete the test all together and just use

Range("A2:M" & LastRowPO).Interior.ColorIndex = 5

or similar.