Extracting Background Colour and pasting it as HEX-Code into a specific cell

ProjectDev

New Member
Joined
Jun 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hey Excelers,
I'm very new to excel macro scripting and recently I wrote something that extracts the data of only coloured cells (in one specific RGB Colour) in a range and only pastes their visual outputs (without formula) in a new worksheet.
Now I'd like to upgrade that script a little bit and make it that it extracts ALL Backgroundcoloured cells within that range - reads their colour and pastes the colour as Hexcode into a designated cell or if possible even within the code of the cell where it found the colour but thats not necessary and probably way more difficult.

Does anyone have an idea of how to do that by any chance?

I hope you all are having a great day and would love to hear from you soon!

Best wishes,
Eric
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ProjectDev

New Member
Joined
Jun 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I think I found a way to print out the desired HEX code, now I just need something that targets all backgroundcoloured cells instead of only the ones colored in a specific backgroundcolour.
Looking forward to your ideas! :)
 

ProjectDev

New Member
Joined
Jun 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
For now I worked around with an else statement - excluding black as a backgroundformat (since uncoloured cells are seen as black), so all coloures are now included except for black, not sure if even black could be included. Thanks alot anyways!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,140
Office Version
  1. 365
Platform
  1. Windows
You could do something like
VBA Code:
   Dim cl As Range
   For Each cl In [a1:c5]
      If cl.DisplayFormat.Interior.Color <> 16777215 Then
         MsgBox cl.Address
      End If
   Next cl
this will ignore any cell with no fill or a white fill
 
Solution

Forum statistics

Threads
1,144,698
Messages
5,725,822
Members
422,643
Latest member
elwayfan446

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top