Is it possible for VBA to copy cell gradient colour to a userform label?

danbates

Active Member
Joined
Oct 8, 2017
Messages
333
Office Version
  1. 2016
Platform
  1. Windows
Hi,

When I open my userform I would like to copy the cell value and colour via VBA to my userform label but the problem is my cell has a gradient in it.

If coping the gradient isn't an option, is it possible for VBA to find 1 of the colours of the gradient?

Basically my cell has a center gradient fill consisting of colour 1 = white and colour 2 = red. I would like the label to be red when the userform is opened.

Lets say my cell is A1 and the userform label is Label1.

Any help would be appreciated.

Thanks
Dan
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Take a look at this and this. Hope they help?
 
Last edited:

danbates

Active Member
Joined
Oct 8, 2017
Messages
333
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Thank you for the links. I've managed to alter the first link to suit my needs.

Thanks again

Dan
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,992
Office Version
  1. 2013
Platform
  1. Windows
Hi,

Thank you for the links. I've managed to alter the first link to suit my needs.

Thanks again

Dan
I have been following this post and it would be nice to see the code you said worked for you.
I looked at the links but did not see a answer that did what I thought you wanted.
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
333
Office Version
  1. 2016
Platform
  1. Windows
Hi,

When I started this thread I wanted the gradient fill to show in my userform but after searching further online and with CSmith's links, I decided to just use a solid fill.

From the first link I used the Select Case part.

VBA Code:
Private Sub UserForm_Activate()

  Dim myRGB_Red As Long
  Dim myRGB_Blue As Long
  Dim myRGB_Green As Long
  Dim myRGB_Yellow As Long
  
  myRGB_Red = RGB(255, 0, 0)
  myRGB_Blue = RGB(0, 112, 192)
  myRGB_Green = RGB(0, 176, 80)
  myRGB_Yellow = RGB(255, 255, 0)
  
    Dim K As Range, where As Range, whatt As String
    whatt = "K"
    Set K = Range("C:C")
    Set where = K.Find(what:=whatt, after:=K(1), searchdirection:=xlPrevious)
    
Dim vVal, vCol
 Select Case ActiveCell
 Case Is = "DB", "AL", "PD"
 vCol = myRGB_Red
 Case Is = "AS", "MT", "AM3"
 vCol = myRGB_Yellow
 Case Is = "RH", "MC1", "MN"
 vCol = myRGB_Green
 Case Is = "GC", "Pete P", "RW"
 vCol = myRGB_Blue
 End Select
 
 With ActiveCell
 Label13.Caption = .Value
 Label13.BackColor = vCol
 End With

 With ActiveCell.Offset(0, 1)
 Label25.Caption = .Text
 End With

 With ActiveCell.Offset(0, 2)
 Label33.Caption = .Text
End With

End Sub

Thanks

Dan
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Glad to see you found a avenue to address your needs :) Thank you for updating us! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,907
Messages
5,544,984
Members
410,647
Latest member
LegenDSlayeR
Top