Stop Shading to Alternate Rows in Excel from getting deleted when data is added

Noidea1970

New Member
Joined
Mar 5, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that I have automated, using 2 buttons to add information and 1 button to delete the information and all tabs that get added.
(The 1st button is a search for and add gather the files I need, the 2nd button actually adds the information from each file).

To make it easier to read all the information I formatted the workbook with alternating shaded rows.
Using the following method. I went to format as a table, choose my color and size. Then choose all the rows and columns I needed.
Checked "my table has headers", Clicked OK
Than I went to convert to range and ended up with what I wanted, see image 1.

When I add the information the shaded rows go back to all white, see image 2.
Image 3 is the code to add the information to Excel. I have 4 workbooks that I use for 12, 25, 37 and 57 parts.
Excel 1.JPGExcel 2.JPGExcel 3.JPG
How can I stop it from going back to all white when the I formation is added?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Question: Why not leave the data as an Excel Table? It manages the shaded rows properly.

You could use conditional formatting to shade every other row. If you filter the data the shaded rows may not be alternating. Copying and pasting conditional formatting duplicates the CF formulas and requires cleanup over time.

A macro could be created to refresh the shading. That's kinda clumsy IMHO.
 
Upvote 0
Found a solution using Conditional Formatting
I found some code that would allow me to choose the colors I wanted to shade both rows.
The 1st line of code will do the odd rows and the 2nd line will do even rows.
The code will only shade rows that have data in them, or as you put more data in, it will keep expanding.
=MOD(ROW(),2)*($A1<>"")
=(1-MOD(ROW(),2))*($A1<>"")
 
Upvote 0
Yes, that was what I was going to suggest if you chose that route. I was once resistive to Excel Tables, now I find them very useful. Conditional formatting will show correctly until you hide rows.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top