Check for gradient fill

  • Hi. In one column I have a list of books. The backgrounds are sometimes a single colour and sometimes have gradient fills. The fills have been done by right clicking the cell, selecting format cell and fill effects and then using a two colour gradient (labelled as Two colors). Is there a way to check if the cell is an ordinary background colour or a two coloured one? The reason for this is that I designate the books to various projects and usually copy/paste the format from a previously formatted cell but I'm trying to automate the colouring from pressing enter on a userform based on a combobox value. I have already found a way to extract the two colours from those cells that have the two colour gradient but I'm struggling to find a way to differentiate between the two formatting types as I have been getting "object variable not set" errors on the non gradient cells..

  • You already have code but as not posted a very quick comment:

    You can check if a cell has a gradient fill by simply querying the Interior.Gradient property. It will return Nothing if no gradient or the type of gradient ('LinearGradient') if there is.

  • I've not posted code because I've spent 2 hours and gotten nowhere. I've experimented with things such as TwoColorGradient Things such as


    ActiveChart.SeriesCollection(1).Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1

    I've found on google as well as


    .OneColorGradient c1f.GradientStyle, _ c1f.GradientVariant, c1f.GradientDegree

    . I was just hoping there would be something a bit more obvious.
    My code would look something like

    If ActiveCell.Fill.GradientColorType = msoGradientTwoColors Then
    MsgBox "Two Colors"
      End If

    where I'd put my what to do with it if it's true stuff.
    Obviously in the examples above I've adapted them to Range("A" & Activecell.row)
    I shall try the LinearGradient you've mentioned later on when I have some time.
    Sorry my post was so brief it's just I have no idea where to start

  • I'm getting a run-time error '438' Object doesn't support this property or method when I use either

    If ActiveCell.Fill.GradientColorType = LinearGradient Then
    MsgBox "Two Colors"
      End If


    If ActiveCell.Fill.LinearGradient = True Then
    MsgBox "Two Colors"
      End If
  • The Gradient of a cell is an object, not a String or Boolean, so you can query the Typename

    Debug.Print Typename(activecell.Interior.Gradient)

    or the actual existence of such an object

    Debug.print activecell.Interior.Gradient is nothing

    My apologies for mentioning a 'Property' ... sometimes distinctions get blurred.

  • Thanks XenoCode. I'm not entirely sure how to use this but I'll do a google search and see if I can muster some understanding. I seem to have gone off on a complete tangent to what I was trying to do.
    Essentially, I have a spreadsheet with a list of books, authors and project associations along with dates etc... and most of the book names are formatted. Some are formatted with a single colour and others are formatted with gradients. Essentially, what I want to do is store the properties of the cells formatting, as I would if I clicked the Format painter. Instead of pasting the format in though, I'd like to store the properties of the formatting of the cell so that I can do a lookup based on the value of a combobox. A bit like conditional formatting if you see what I mean. I know this is a bit off topic with the thread but I just figured it probably made sense for me to explain what I'm up to. I've found a way of extracting the colouring information from a cell without the gradient, and I've found a way of extracting the information from gradients but it won't let me differentiate between the two, which is why I was looking for a way to check the gradient status.
    Thanks so much for your time and patience with me on this matter.

  • Hello mmr,

    Would you mind posting a sample sheet which would illustrate your problem ... along with your expected result ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi. I've attached a simplified version of the sheet I'm working on with some comments in the code from the enter button on the form. It's a bit off topic from what I started this post about but it shows what I was trying to do. Incidentally, if there is a solution to what I've posted I'd be interested to know it as I dislike to not be able to find solutions to things.

  • That's what I want to do but I've been using this spreadsheet for 6 + years and so have the formats already set and so I'd like to find a way to extract the formatting details for each record so as to keep them the same. Ideally I'd like to use something to get the results from the paintbrush icon if that was at all possible. I did find some code to extract each gradient point so it's possible I could do it that way. However, my curiosity has now got the better of me so I'm hoping to find a way to extract the formatting somehow

  • I'm not familiar with the gradient fill so what I would do in these circumstances is to record a macro and that should you give you the various properties and methods. You can then store the properties in an array or similar and apply them to other cells.

    You can of course Copy and Paste formats.

  • I've amended your function slightly to return the colour of the non-gradient cells too. Here is my amended code

    Function Grad(rng As Range) As String
      If rng.Interior.Pattern = 4000 Then
        Grad = rng.Interior.Gradient.ColorStops(1).Color & "   -   " & rng.Interior.Gradient.ColorStops(2).Color
        Grad = rng.Interior.ColorIndex
      End If
    End Function

    Thankyou so much. That does everything I was looking for.

  • Glad you could fix your problem ... :wink:

    Thanks a lot ... for your Thanks AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!