Excel Change Color in Range in Alternating Columns

We "stack" uniform spreadsheets (one below the previous) into a Master spreadsheet, but the column colors from the various spreadsheets vary. The columns run G:EL, and the range within each column is Row 4 : Row 627.

We trying to find code like:

=IF((G3="Discount"),change[G4:G627].Interior.ColorIndex=22, IF((G3="Min Chrg"),change[G4:G627].Interior.ColorIndex=30, "") .  

Row 3 has the two column labels, that alternate for each column. But it needs to be applicable to columns G through EL.

Any insight or suggestions would be greatly appreciated.

Tosagua
TosaguaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
This sounds like a situation that could be solved with Conditional Formatting.  However, the issue will be how to get subsequently pasted rows of data to follow your color scheme (i.e., having a set of conditional formatting rules that are open-ended to test new data blocks that are appended).

VBA (macro) could resolve this, but you didn't state if that was an option.

Regards,
-Glenn
0
TosaguaAuthor Commented:
We definitely need a macro.
Columns G:EL should not change, and the rows 4 to 627 should be fairly stable, but we can update the macro if needed before we run it..
We want to stay away from Conditional Formatting, because we can use it to find errors.
i.e. If some meant to enter a 10.0%, Discount, but entered 100.% Discount (no charge), it would an error that we could use CF to spot.

Tosagua
0
Glenn RayExcel VBA DeveloperCommented:
I'm restating to make sure I understand:

You paste/append blocks of data (624 rows each) in columns G:EL of a Master worksheet.
If the value in column G is "Discount", then the block of cells from columns G:EL and 624 rows down should have the interior color changed to 22.
If the value in column G is "Min Chrg" then, the block of cells (again, G:EL and 624 rows down) should have the interior color changed to 30.

Is this correct?

-Glenn
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

TosaguaAuthor Commented:
Glenn,

Pretty close.
ALL of the spreadsheets together will be 624 rows in total.

The pattern of the columns is:
G3=Discount,  H3 = Min Chrg,  I3=Discount, J3=Min Chrg. etc. to column EL

G4:G624 should have interior color 22.
H4:H624 should have interior color 30.
I4:I624 should have interior color 22.
J4:J624 should have interior color 30. etc., to column EL

The Range of cells is very stable.

Tosagua
0
Glenn RayExcel VBA DeveloperCommented:
Tosagua,

IF you're actually pasting data side-by-side in this manner and the value in row 3 will be the determining factor as to the background color of the 624 cells below, then I highly recommend using Conditional Formatting.

Even if you want to add other CF tests for error checking as you previously mentioned (ex., percentage tests), you should be able to create just two conditional formatting rules that will do all this work across the sheet.

I've attached an example file that does just this.  Note the two formulas:
=E$3="Discount" applies to range =$E$4:$EL$627
=E$3="Min Chrg" applies to the same range.

You can add additional formulas that will test for out-of-range values; I've added an example one in the workbook to show this.

Regards,
-Glenn
EE-Conditional-Format-Cols.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Tosagua, if your columns and rows are stable as you say, why not just format the cells and then when new data is pasted in it gets pasted as values and doesn't overwrite the formatting.

Thanks
Rob H
0
TosaguaAuthor Commented:
Rob,

The problem is the formatting of the columns alternates between "00.00%, and $00.00.
i.e.; G, I, K, are formatted 00.00% and H, J, L, are formatted $00.00, all the way to column EL.
This is why we need to alternate the column colors, one color for percentages, and one color for dollars.
Visually, it's easier to keep track of where you are at, when looking up carrier rates, for a specific state.

Tosagua
0
Rob HensonFinance AnalystCommented:
So format with colour and number format. Format 2 columns as required and then copy paste formats every other column.
0
TosaguaAuthor Commented:
Rob,

I should have provided an example at the outset, and a little more background. Attached is an abbreviated version of what we already have. I do not know why the people submitting the rates just can't leave the column colors as they are presented, but most need to change the column colors to suit their taste, before sending them back. Then we end up with a jumble of colors.

Then once we complete creating this spreadsheet, someone needs to submit new rates. Notice the dates under the carrier. So we take the new rates and paste them over the old rates, which is why we were looking at a macro or conditional formatting to restore the original column colors on the new rates.

I'm trying to get the Conditional Formatting to work per the example, but all of the columns go to one color or another.

Tosagua
Sample-Rates.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
Tosagua,

Is there any reason why the data can't be pasted using PasteSpecial...Values?  That would allow you to set up the interior color for each column in advance and ensure that it isn't changed.

-Glenn
0
TosaguaAuthor Commented:
Glenn,

I don't know what happened, but my last Comments aren't here. It may have to do with our installation of Internet Explorer 11. But in short, I could not get the Conditional Formatting formulas to work, and we couldn't figure out why ? We brought up your example with the formulas on one screen, and our attempt on another screen, and two people reviewed them. The only difference was that you started in Column E, and we started in Column G, and this was reflected in the formulas. Everyone agreed that our formulas should have worked like yours.

So we tried two different formulas in the Conditional formatting.
=MOD(COLUMN(),2)=1 , which applies the color formatting to the odd numbered columns in the given range, and
=MOD(COLUMN(),2)=0, which applies the formatting to the even numbered columns in the given range.
This worked. Who knows ? So, since you got us to look at Conditional Formatting, we'll award you the bulk of the points. However, Rob has really pointed out what should have been obvious to us: Format the worksheet FIRST, then add the data.

We thank you for your assistance.

Tosagua
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.