Experts Needed for Color Coding

[Bettergains]

Hello: this ought to be a challenge.
I have a spreadsheet with 10 columns
Col A contains an ID
There are various color coded cells in each;
Row 1: B - C are red; F is yellow
Row 2: C is green; E is yellow, etc.
Row count could be up to 300

Objective:
Before refreshing this report (from Access query)
1. identify cell color for cells in rows
2. After refresh, find row (per the ID field) and replicate the color

Conditions:
New unique rows may be added during the refresh; these will not be color coded
The color is applied without consistent rules in many cases
Rather than refreshing the report, I can also append the new rows first and
then organize.
I'm thinking I may need a color code column in the query, something like:
ID & b19 (for col + color) and then format the cells using this code. If
there is another better way, I'm all ears.
Thanks in advance for your expertise.

 

[Dave Peterson]

I don't work with queries, but maybe you could do this.

Before you run the query to update the values, save that worksheet with all its
formats to a new name. (I'd just insert a new sheet, copy the old range and
paste special|values and Paste special|formats--you'd only really need the
values for the key column--we'll ignore those values in the other column.

Then update your values with your query.

Now you can use the key column to look for a match.
If you find one, copy|Paste special|formats over the new data.

If that sounds reasonable, how about:

Option Explicit
Sub testme()

Dim OldWks As Worksheet
Dim NewWks As Worksheet

Dim OldKeyRng As Range
Dim NewKeyRng As Range

Dim res As Variant
Dim myCell As Range

Set OldWks = Worksheets("sheet1")
Set NewWks = Worksheets("sheet2")

With NewWks
Set NewKeyRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With OldWks
Set OldKeyRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In NewKeyRng.Cells
res = Application.Match(myCell.Value, OldKeyRng, 0)
If IsError(res) Then
'no match, do nothing
Else
OldKeyRng(res).Resize(1, 10).Copy
myCell.PasteSpecial Paste:=xlPasteFormats
End If
Next myCell

End Sub

Change the names of those sheets to match your situation.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Bettergains]

Hi Dave: thanks. I'll give it a try. After a quick review: what is "res"
and why are you using Variant? Shouldn't the xldown be xlsup? Anyway, I'll
step through it and perhaps answer my own questions. This is better &
simpler than my original approach.

 

[Dave Peterson]

res is a variable that is used in this line:

res = Application.Match(myCell.Value, OldKeyRng, 0)

If you use =match() on a worksheet, you'll see that it either returns a number
(when there's a match) or an error if there is no match. So res can be numeric
or an error. That's the reason that res is declared a variant.

In this kind of code:

With NewWks
Set NewKeyRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

I'm starting at A1, but then it's going down to the lastrow in that column
(A65536), then going up until it hits a value/formula in the cell.

It's like selecting A65536 and then hitting the End key, then uparrow if you
were doing it manually.

If you knew that you didn't have any gaps in your data, you could start at the
top and work down:

With NewWks
Set NewKeyRng = .Range("a1", .range("a1").End(xldown))
End With


I generally like starting at the bottom and working up if I don't know the data.

 

[Bettergains]

Dave:
This was very helpful, and I will be using this a lot. Thanks bunches.

 

[Dave Peterson]

Good luck,

Dave:
This was very helpful, and I will be using this a lot. Thanks bunches.