Author Topic: Excel Question  (Read 2901 times)

0 Members and 1 Guest are viewing this topic.

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Excel Question
« on: September 19, 2019, 08:23:49 pm »
I'm creating a spreadsheet and would like to color every other number of rows (more than one).

Unfortunately it gets a bit more complicated. A particular item may have six rows of data associated with it while another item may have seven.

Can I check whether B3 contains text, and, if so, color that row plus the next X rows until Excel sees text in the same column (say B8)?

Here is an example:

<A1> <part number> <data>
                                 <data>
                                 <data>
                                 <data>

<A5> <part number> <data>
                                 <data>
                                 <data>

So I'd want the four rows for A1 to stay white, and the three rows for A5 to be a different color. Then the next time something shows in column B, it makes it white.

 

Offline ledtester

  • Super Contributor
  • ***
  • Posts: 3034
  • Country: us
Re: Excel Question
« Reply #1 on: September 19, 2019, 11:36:46 pm »
Create a new hidden column which will contain either a 0 or a 1.

The formula for that column is:

If the part number field is populated, value = 1 - the value in the previous row.
Otherwise the value = the same value in the previous row.

You'll have to add a new row at the top to specify an initial value for this column (either 0 or 1).

This will group together rows which belong together and alternate the value among the groups.

Now you can color the rows based on this column.
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #2 on: September 20, 2019, 03:28:55 am »
How would it automatically color an entire row?

Below is an example of the locations data would be in. I'm using the actual Excel locations to represent data in those cells. The actual data is in many more rows, so data would be from A1 to H1. So in this example, I'd need to keep A1 to A4 white but across to H1 to H4, A5 to A6 blue but also across to H5 to H6, next set of data (which may be less or more rows than previous) white, etc...

This will be over 100s of rows and across to around column H.

<A1> <B1> <C1>
         <B2> <C2>
         <B3> <C3>
         <B4> <C4>
<A5> <B5> <C5>
         <B6> <C6>
 

Offline ledtester

  • Super Contributor
  • ***
  • Posts: 3034
  • Country: us
Re: Excel Question
« Reply #3 on: September 20, 2019, 03:39:41 am »
« Last Edit: September 20, 2019, 03:42:54 am by ledtester »
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #4 on: September 20, 2019, 05:55:26 pm »
Adding a column to use as 0s and 1s is a great idea.

I'm having trouble trying to create a formula that would flip the 0s and 1s though.

Currently I have data in B3 and I need from B3 until B8 to remain white. At B9 is a new piece of data, and I need from B9 to B14 to be shaded (which I'll use conditional formatting for), and then at B15 to etc... to be white, back to shaded, etc...

So I need a statement that will keep the column at a '1' from a cell that has data, the following empty cells, and then flip to '0' when it sees a cell with another piece of data.
 

Offline ledtester

  • Super Contributor
  • ***
  • Posts: 3034
  • Country: us
Re: Excel Question
« Reply #5 on: September 20, 2019, 06:27:54 pm »
Let's say your new column is Z.

Z1 = 0
Z2 = IF A2 is populated THEN 1-Z1 ELSE Z1
Z3 = IF A3 is populated THEN 1-Z2 ELSE Z2
Z4 = IF A4 is populated THEN 1-Z3 ELSE Z3
...
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #6 on: September 20, 2019, 07:24:03 pm »
That worked, thanks.

Now I have a nice column of 0s and 1s. I applied conditional formatting to the entire row based on whether Z1 = 1. Basically, if Z1 > 0, then format the cell blue.

When I try to apply conditional formatting to all other rows, it bases the rule on Z1 only and doesn't increment the rule Z2, Z3, etc....
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #7 on: September 20, 2019, 07:31:40 pm »
Never mind, I figured it out.


Thanks anyway and thanks for the help.
 

Offline coolyota

  • Contributor
  • Posts: 18
  • Country: au
Re: Excel Question
« Reply #8 on: September 24, 2019, 08:43:23 am »
Late to the party, hope this helps you  :)

Assuming the data set...
842154-0

Push the big button.
842158-1

Alternating rows are highlighted
[ Specified attachment is not available ]


VBA code:
Code: [Select]
Option Explicit


'Settings
Const FIRST_COL As String = "A"
Const prev_COL As String = "F"



Sub main()

    'Define colour array
    Dim colour(1) As Variant
    Dim colour_flag As Byte
   
    'Define the colour in RGB here
    '255,255,255 = white. Experiment with different values.
    colour(0) = RGB(255, 255, 255)
    colour(1) = RGB(111, 222, 255)
    colour_flag = 1
   
    Dim current_col1_val, current_col2_val As String
    Dim prev_col1_val As String
    Dim row As Long
    Dim highlight_first_row, highlight_prev_row As Long
   
    'Activate sheet
    Sheets("Data").Activate
    'Start row
    row = 2
    highlight_first_row = 2
    highlight_prev_row = 2
           
    'Get values
    current_col1_val = Range("A" & row).Value
    current_col2_val = Range("B" & row).Value
    prev_col1_val = current_col1_val
 
    'Loop until last row
    While current_col2_val <> ""
       
        'If current data in column A is different from previous row, AND previous data is empty then...
        If (current_col1_val <> prev_col1_val And prev_col1_val = "") Then
            'Switch colour
            colour_flag = colour_flag Xor 1
            Call Highlight(highlight_first_row, row - 1, colour(colour_flag))
            'Make current row the new start row for next set of highlights
            highlight_first_row = row
        End If
       
        'Save current data in column A
        prev_col1_val = current_col1_val
   
        'Move onto next row
        row = row + 1
        current_col1_val = Range("A" & row).Value
        current_col2_val = Range("B" & row).Value
    Wend
   
    colour_flag = colour_flag Xor 1
    Call Highlight(highlight_first_row, row - 1, colour(colour_flag))
   
   
   
   

 

End Sub



'Highlight cells
Sub Highlight(ByVal first_row As Long, ByVal prev_row As Long, ByVal colour As Variant)
   
    Range(FIRST_COL & first_row & ":" & prev_COL & prev_row).Interior.color = colour

End Sub



How to add VBA to Excel: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #9 on: October 10, 2019, 08:04:14 pm »
After spending the last few weeks on a project and incorporating the conditional formatting per the suggestions of others, I discovered an issue.

I need to create a basic spreadsheet without formulas and remove the 0s and 1s that help the conditional formatting. If I copy/paste it into a blank sheet without the 0s and 1s, it removes the conditional formatting color. I've searched online and found two options:

Paste the entire thing into Word, and then paste it back into a spreadsheet (seems to work well)

Entire code (which I'm uncertain how to execute) into Excel - but it's all homemade code.

Any ideas on how to make conditional formatting static?

 

Online Ian.M

  • Super Contributor
  • ***
  • Posts: 12837
Re: Excel Question
« Reply #10 on: October 10, 2019, 09:02:00 pm »
Is keeping the conditional formatting but 'flattening' the column of formulae that control it to literal 0 and 1 values acceptable?

If so, simply copy the column and paste it back using 'Paste Special', with 'Values' selected.

If not, it gets really gnarly.  I am not aware of any way to do it cleanly* without a lot of VBA scripting.  Others have done the heavy lifting for the scripting: https://www.mrexcel.com/forum/excel-questions/284013-convert-conditional-formatting-into-standard-one.html but a fair bit of debugging is likely to be required to get it to 'flatten' the correct attributes from conditional to normal formatting.

*The hacks of  paste to Word and copy back or save as HTML and reopen are likely to screw up fonts, layout etc.
« Last Edit: October 10, 2019, 09:03:57 pm by Ian.M »
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #11 on: October 11, 2019, 12:57:03 pm »
Quote
If so, simply copy the column and paste it back using 'Paste Special', with 'Values' selected.


Having a spreadsheet with only "values" is perfectly fine. The final spreadsheet needs to be presented and can't have formulas anyway, however, when I (initially) tried this prior to posting the message, I realized it still carried conditional formatting with it.

The link you provided is a site I already read, however, I don't know how to implement the code into Excel. Any assistance will be appreciated.

 

Online Ian.M

  • Super Contributor
  • ***
  • Posts: 12837
Re: Excel Question
« Reply #12 on: October 11, 2019, 01:55:06 pm »
Implementation depends on your Excel version.  As I strenuously avoid using any MS Orifice applications with a ribbon, and use Excel 97 for my own document creation needs, I cant be much help for anything past Excel 2000.  Make sure you have the VBA* help file installed and start digging into recording and running a macro.  Do something simple like putting an ordinary format on a single cell then see if you can find and open the VBA source of the macro and edit it to do what you need.

* VBA: Visual BASIC for Applications - a cut-down version of Microsoft's Visual Basic - an object-oriented dialect of BASIC - used for macro scripting in many Microsoft productivity applications.
« Last Edit: October 11, 2019, 07:58:02 pm by Ian.M »
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Excel Question
« Reply #13 on: October 11, 2019, 04:08:43 pm »
Try the attached.

When you run it, select the region you want to flatten the values and formatting, then click the button.
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Excel Question
« Reply #14 on: October 11, 2019, 05:05:24 pm »
I just noticed your username. Chances are good we're just a few miles apart (West Cambridge here).
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #15 on: October 15, 2019, 02:14:21 pm »
I'm a bit north of you, closer to Tewksbury.

I need to use that file on my work PC, so I'm uncertain whether it will be allowed. What is the file?
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Excel Question
« Reply #16 on: October 15, 2019, 03:24:23 pm »
It's an excel file inside the zip file. (It has to be in a zip file because eevblog doesn't allow excel uploads.)

I did it on Excel 16.29.1 on Mac, but it probably works on any of the last 5 years of Excel or thereabouts.

If you need it in an earlier format of excel, I might be able to dig that up or can just post the code here for you to copy/paste into that earlier version.
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #17 on: October 15, 2019, 03:40:32 pm »
Quote
I just noticed your username. Chances are good we're just a few miles apart (West Cambridge here).


By any chance, are you looking for any test equipment? I'm selling three logic analyzers and a low voltage precision power supply for cheap. Since you're relatively local to me, thought I'd ask.
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #18 on: October 17, 2019, 07:30:55 pm »
The file you sent works well.

Two flaws:

I need to copy that tab into my document because I can't carry over Conditional Formatting into your document. Not a big issue, and easily worked around. Basically copy the tab and reapply Conditional Formatting before running the macro.

The main issue: it flattens the spreadsheet with a pre-selected color (kind of a tan color). Any way to change the color? Also, what if I have different colors in different cells for different Conditional Formatting rules? If so, it will change them to the same color.
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Excel Question
« Reply #19 on: October 17, 2019, 07:44:14 pm »
I need to copy that tab into my document because I can't carry over Conditional Formatting into your document. Not a big issue, and easily worked around. Basically copy the tab and reapply Conditional Formatting before running the macro.
Copy the Macro code and save it in either the original workbook, or in a personal workbook and run the code right from the original spreadsheet.
https://support.office.com/en-us/article/create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790

Note that the column A formula will need to be applied into the original workbook (or another solution for the conditional color-coding) in order to get the color coding to work.

The main issue: it flattens the spreadsheet with a pre-selected color (kind of a tan color). Any way to change the color? Also, what if I have different colors in different cells for different Conditional Formatting rules? If so, it will change them to the same color.
That should not be the case. It should be flattening the spreadsheet with whatever color formatting the original cell contents had. I think this is a side-effect of you copying the data from your original sheet into the sheet I sent and if you follow the recommendation above (to run the code in the original workbook), that this issue will be automatically eliminated.

If it is still an issue, let me know and I'll be happy to look into it.
 

Offline bostonmanTopic starter

  • Super Contributor
  • ***
  • Posts: 1773
  • Country: us
Re: Excel Question
« Reply #20 on: October 23, 2019, 06:06:46 pm »
I've tried various methods and all I get is black and white in a new spreadsheet.

Here is an example of what my spreadsheet looks like. The 0s and 1s on the right are what I'm using to make the alternating white/green, and the left is a modified formula from your spreadsheet.




 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Excel Question
« Reply #21 on: October 23, 2019, 06:13:20 pm »
Zip up what you have and post it, or mail me a copy and I'll have a look and almost surely figure it out.

I'll PM you my email.
 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf