1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel VBA copy cell fill colours and modify

Discussion in 'Business Applications' started by Gram123, Oct 1, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hi,
    I'm currently writing a macro, part of which necessitates a different fill colour being assigned to each cell in a range - from B28 to the last cell in column B where there is corresponding data in column C. This could potentially be up to 50 cells in any single worksheet (one month's Arrivals), so 50 different colours, but will more likely be somewhere between 15 and 30.

    I found some code that randomly applies the fill colours, and modified it, as follows:

    This works pretty well, but there's always the chance of 2 very similar colours appearing consecutively, which I could do with avoiding...

    As mentioned these colours are for Arrivals. I also need to apply colours for Estimated Arrivals in column A. These should be lighter shades of their counterparts in column B.....
    In other words, if cell B28 is filled with a bright green colour, I need A28 to be be a paler green colour (maybe 25% lighter ?).
    I can copy the range and paste in column A, but don't know how to then modify the fill colours by degrees or otherwise reduce their brightness.

    It may be that this is not possible, or there's a better way to go about this. It may be that I need to set static colours, but obviously I'd rather not hard-code in 50 colour refs (or 100 if there's no way to take a colour and make it paler).

    Any advices?!
    Cheers.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Hi,
    I use constans for color values which I apply when needed

    Code:
    Public Const lBlue      As Long = 16764057
    Public Const lViolet    As Long = 16764108
    Public Const wBlank     As Long = 16777215
    Public Const dBlue      As Long = 9592886
    Public Const lGrey      As Long = 12632256
    Public Const lGreen     As Long = 13434828
    Public Const lPink      As Long = 14408946
    Public Const lLavend    As Long = 16764108
    Public Const rustRed    As Long = 3421846
    
    And then use the code as follows

    .Cells.Interior.Color = rustRed

    This isntead of RGB

    You've also got the default colors like:
    vbRed, vbBlue, vbYellow, etc.

    Hope this helps
     
  3. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    The problem is, I'd have to put 50 constants in code, and I still don't know how to make lighter versions of those colours
    i.e. it doesn't look like a simple calculation would work:

    .Cells.Interior.Color = rustRed - 10000
     
  4. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Ok, say I have a list of 50 values in C1:C50.
    I want to apply cell shading to B1:B50, a different colour per cell. These should be fairly bright to dark colours.
    Is there a way to do this in VBA without hard-coding 50 lines of colour refs?

    The random colouring generated by the code I posted above almost works, but there's too much chance of very similar shades appearing next to one another.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Probably, but you have to 'tell' it something, You start with a color value.
    If you do it randm with RGB(x,y,z) you don't know what colors will be.

    Create a sheet with the values and see which color is what and maybe from there you could see if a random color series will be helpful.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Hi Gram,

    I was just playing aorund:

    Paste the code below in the sheet's vba and run it

    Code:
    Sub testing()
    Dim cell As Range
    For Each cell In Range("D2:H15")
        cell.Interior.Color = 255455 + cell.Row * WorksheetFunction.RandBetween(cell.Row, 300)
    Next cell
    End Sub
    
    Maybe it's something you can use :)
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    It's more fun like this:

    Code:
    Sub testing()
    Dim cell As Range
    Dim i As Integer
    For i = 1 To 100
    For Each cell In Range("D2:H15")
        cell.Interior.Color = 255455 + cell.Row * WorksheetFunction.RandBetween(cell.Row, i * 50)
    Next cell
    Next i
    End Sub
    
     
  8. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Thanks for trying Keeb.
    I had a play with the code you provided, but it looks like there's still too much chance of similarly coloured cells being generated near one another which could cause confusion. And with the fun version, I eventually made Excel say "too many cell formats", which is an error I've never seen before!


    Ok, change of plan (and a related issue).
    Whilst I might get up to 50 rows of data, some values will appear in that data more than once. The coloured cell for each instance of the same value, therefore, can be the same colour. So I shouldn't need quite as many as 50 bright shades and 50 lighter shades.

    I've come up with a worksheet containing a bunch of bright colours and paler versions - see attached.
    My idea is that I'll also put sequential numbers in these cells or a relevant short text value based on the data. At each change of value in the Value column, the next pair of colours should be applied to the corresponding cells in columns A and B.

    The Result worksheet in the attached Excel file makes it clear what I hope to do.
    The possible colours from the Colours worksheet could be hard-coded, or if necessary, I can leave them on that worksheet and hide it.

    My related issue is with applying cell shading (banding) for the rest of the data. This should be like the alternate row shading applied by an Excel table, but instead of it alternating per row, I'd need it to alternate per Value. Again, if you view the attached and scroll down, you'll see what I mean.
    I know this can be done with conditional formatting, but would rather keep it all to code if possible.

    Thanks in advance for any help!!
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    I don't know if I got the whole idea.

    Dou you want to look up the value and then use the color in that extra table?
     
  10. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    No, I don't need it to perform lookups as such. I think naming the worksheet "Result" has confused things.
    The data will be on the Result sheet already. I just need to apply the colours to rows containing data.

    So...
    - In the example file I posted, on the Result worksheet, there is data in cells C4:D18.

    - As there is data in row 4, I need the code to grab the first colour pair (cells A1 and B1 on the Colours worksheet) and apply it to cells A4 and B4 on the Results sheet.

    - As there is data in row 5, the next pair of colours grab the second colour pair (cells A2 and B2 on the Colours worksheet) and apply to cells A5 and B5 in the Results sheet.

    - The data in row 6 is the same as in row 5, so cells A6 and B6 should be filled with the same colours as A5 and B5.

    and so on.

    If there happened to be more than 30 rows of data on the Results sheet, I guess it would be best to start cycling through the colours again from the top.

    EDIT: As aforementioned, I'm not sure if the colours are best stored on a worksheet like the example file, or hard-coded. If you think it's wiser to have them in code, I can supply the hex or RGB colour refs...
     
  11. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Assigning the text values to the coloured cells would be easy, as they'd just refer to the cells in the Value column - e.g. in cells A4 and B4, the formula would just be =$C4.

    To apply sequential numbering instead, starting with a 1 in cells A4 and B4, it would then presumably need to add 1 to that for each subsequent row where the values in the Value & Field cells change. Where they don't change, the same number should be repeated (just like the cell shading).

    Then for the colour banding of cells C:D, the same thing again, alternating between pale blue and very pale blue every time the values in the Value & Field cells change.
     
  12. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Ok, I found how to do the colour banding:

     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    That's not what you were asking for initially ? :confused:
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
  15. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    No, I know, I still need to apply the fill colours to columns A & B. But the two are related.
    The colour banding code I posted applies alternating colours to the rows (in columns C & D) every time there is a different value in C.

    Whatever method is used for applying the various fill colours to the columns A & B, every time there is a different value in C I will need the next colour pair applying - so the For Next loop will likely be similar.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1134634

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice