Fill color cannot remove?

bobkap

Board Regular
Joined
Nov 22, 2009
Messages
241
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I used a macro I pulled from a MrExcel book to color cells red if a certain condition was met. That worked just fine. BUT, now I want to manually change the color of a few entries. I went to the paint bucket icon on the home ribbon and selected "no fill", but nothing happened.

This is a sample of the data I have. I had to use a picture as the actual copy/paste would not show the colors.
1599505567934.png

Here's the code used to do the coloring.
VBA Code:
'Highlight unique email addresses
With Range("C2:C566")
        .FormatConditions.Delete
        .FormatConditions.AddUniqueValues
        .FormatConditions(1).DupeUnique = xlUnique
        .FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    End With

Isn't "interior.color" the same thing as what you get when you use fill colors?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
The colours are being set by conditional formatting, which overrule manually coloured cells.
To change the colour of some cells, you will need to remove the conditional formatting
 

bobkap

Board Regular
Joined
Nov 22, 2009
Messages
241
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
The colours are being set by conditional formatting, which overrule manually coloured cells.
To change the colour of some cells, you will need to remove the conditional formatting

Thanks very much. My real goal is to find if a certain cell has a certain color or not, but when I use this code I get the same results regardless of the color in the cell. If it's a red cell or no-fill cell, I still get the same number in return. Might you know why that happens?
VBA Code:
ActiveCell.Interior.ColorIndex
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,850
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
It is because conditional formatting doesn't come under the Range.Interior, if you are using a reasonably modern version of Excel (please consider going into your account and amending your profile to show your operating system and Excel version as it does affect whether the answers given work) then try
VBA Code:
MsgBox ActiveCell.DisplayFormat.Interior.ColorIndex
 

Watch MrExcel Video

Forum statistics

Threads
1,122,748
Messages
5,597,914
Members
414,190
Latest member
PuzzlerUK

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