0

The code below terminates prematurely and thus never sets the Interior Color. Any idea what I'm doing wrong?

A second problem is that it doesn't seem to be grabbing the background color for argument "c" properly. It seems to always use white (16777215) for the background color, even when I've set the background color to something else.

Function Darken(c As Range) As Long 
chg = 0.8 
Dim clr As Long

 clr = ActiveSheet.Cells(c.Row, c.Column).Interior.Color  '<< ALWAYS GRABS 16777215, REGARDLESS OF ACTUAL BACKGROUND COLOR
 cRed = clr Mod 256 
 cGreen = (clr \ 256) Mod 256 
 cBlue = clr \ (65336)

 ActiveCell.Interior.Color = RGB(Int(cRed * chg), Int(cGreen * chg), _
 Int(cBlue * chg)) '<< CODE TERMINATES HERE PREMATURELY. WHY DOESN'T IT LIKE THIS?

 Darken = clr 
End Function
Crowdpleasr
  • 3,574
  • 4
  • 21
  • 37
  • `clr = c.Cells(1).Interior.Color`? How are you calling this? – BigBen Mar 25 '20 at 19:23
  • You have no explicit references on your range objects that's probably causing the white color return, whats the error when it stops? – Warcupine Mar 25 '20 at 19:24
  • `Darken` should return a `Long`, not an `Integer`. – BigBen Mar 25 '20 at 19:25
  • I changed it to ActiveSheet.Cells(c.Row, c.Column).Interior.Color, but it still picks up 16777215. I also changed Darken to return a Long rather than Integer. Regard the error message, there's no error message. The code just stops prematurely, the cell (ActiveCell) doesn't change color, and I get a "#VALUE! in the cell (ActiveCell). – Crowdpleasr Mar 25 '20 at 19:29
  • is the color by conditional formatting? – Scott Craner Mar 25 '20 at 19:30
  • You can't change the format using a UDF like that.... – BigBen Mar 25 '20 at 19:32
  • 1
    Also if you are calling this from the worksheet as a UDF, it will not work. A UDF called from the worksheet cannot change the format of any cell. It can only return a value to the cell that calls it. – Scott Craner Mar 25 '20 at 19:32
  • @BigBen: you can but it is not advisable :) – Siddharth Rout Mar 25 '20 at 19:33
  • @SiddharthRout - well I didn't add that it can be done in some edge cases, yes. – BigBen Mar 25 '20 at 19:33
  • As stated there are some work arounds: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet But it is not advisable. – Scott Craner Mar 25 '20 at 19:33
  • @BigBen - Many thanks for the suggestion, but I can change the color fine in the immediate window using basically the same code/format as is in my code . . . – Crowdpleasr Mar 25 '20 at 19:33
  • See Scott's comment... the Immediate Window is a different situation. – BigBen Mar 25 '20 at 19:34
  • 1
    @Crowdpleasr yes a function called from the vbe will change the format, but not called from the worksheet. – Scott Craner Mar 25 '20 at 19:34
  • 1
    As far as grabbing the white, my guess is that the color is added using Conditional Formatting which will not show with `.Interior.Color ` one must use `.DisplayFormat.Interior.Color` but that will not work with UDF called from the worksheet: https://stackoverflow.com/questions/45122782/how-to-get-the-background-color-from-a-conditional-formatting-in-excel-using-vba – Scott Craner Mar 25 '20 at 19:40
  • You're right, @Scott Craner. The color was added using Conditional Formatting. Many thanks for all the assistance! – Crowdpleasr Mar 25 '20 at 19:42
  • I tried this out and it is working fine for me, regardless of where I call the function from or where I create the function. It even works when I try with different colors. I'm guessing there is an issue with the parameter you are sending, it doesn't seem to be an issue with the function itself. – SendETHToThisAddress Mar 25 '20 at 21:22
  • @technoman23 - as noted in a previous [comment](https://stackoverflow.com/questions/60855947/not-able-to-set-activecell-interior-color#comment107670708_60855947), calling this as a UDF in a worksheet cell will not work. – BigBen Mar 26 '20 at 03:49
  • Thanks, @technoman23. Can you please confirm you're calling it with a User Defined Function in your spreadsheet, for example using something like: =Darken(C2) as a formula in a cell? – Crowdpleasr Mar 26 '20 at 06:02
  • Ah I see, you are trying to set the color using the formula bar, and calling the function from the cell formula. I don't think it's possible to do this. But even if you could I don't think you would want to because then you couldn't put a value in that cell. You might trying calling the function using the on Worksheet_Change instead. – SendETHToThisAddress Mar 26 '20 at 11:57
  • Thank you to everyone. (Warcupine, Siddharth Rout, BigBen, ScottCraner, technoman23). If someone wants to answer as per the comments above, I'll select that as as answer, or otherwise I'll answer it myself and close out the discussion. – Crowdpleasr Mar 26 '20 at 15:08

0 Answers0