Email:      Pass:    Pass?

Free Excel Forum

Conditional Formatting - Making Value Equal To Cell Color To Hide It

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I would like to hide the value in the cell when I using conditionla formatting or at least make it the same color of the cell color (so will be hide)

Really appreciate help

Similar Excel Video Tutorials

Helpful Excel Macros

Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Hide Specific Comments in Excel - Comments Will Still Display on Hover
- Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Hide Comments in Excel Completely - Even Indicators Will not Appear
- Hide all comments in an Excel workbook. No indicators will be displayed and comments will not appear when you hover ove

Similar Topics

I have data in this range: A11:C17. For a few cells I am getting a error message #VALUE! and I know why. I would like to use the iserror command and conditional formatting [change color of font to match cell color] to hide this stuff. I am using the formula below but it isn't working. Can someone show me how to modify it?



For various reasons I need to be able to hide certain cells, but I cannot hide it by changing the font color to the background because the background color is not consistent. To go through and select the background color would be just as time consuming as individually deleting the contents of the cell.

One of the reasons I am doing this might be able to be solved in a different way:
I am summing the percent complete in a column so it gradually gets closer to 100%. For many rows though it reaches 100% before the end, so I have many consecutive cells showing 100%. I would like to only show the first time it reaches 100% and then not show anything after that.

Thanks for the help.

I should also note that if there is a way in conditional formatting to choose "background color" rather than selecting the color that I know the background is, that would be acceptable, but I do not know of how to do this if it is possible.

I have a table from A3:M8 and in row B7:M7 I am calculating percentage by dividing B6/B4 but in some B6 or B4 cells i don't have any data which shows #DIV/0! in corresponding B7 to M7 cell.
I want to hide this error message.
i tried using conditional formatting to make the #DIV/0! to white font color but it didn't work. Any idea to hide this value?

VBA might not even be necessary for this but this is what I need. I have a range of cells that are already performing a Vlookup and what I want is if it comes back as less than 95% color the cell orange but if the cell is blank then no format. Also, if the vlookup comes back as #N/A then I want it to hide that entry by making the text the same color as the background so you do not see it. Any ideas on how to do this? Thanks for the help.

Just to illustrate. This is what I need except I need the #N/A to have the font color the same as the background so it is not visible.

50.00% 50.00% #N/A

I know you can use Conditional Formatting to allocate a colour for a cell
value but you are limited to 4 alternatives (ie the default cell color and
only 3 variations in Conditional Formatting).

How can I allocate a different color for 5 different cell values?

For example:
If cell value = 1 then cell color is green
If cell value = 2 then cell color is blue
If cell value = 3 then cell color is yellow
If cell value = 4 then cell color is red
If cell value = 5 then cell color is magenta



Based on what is input into a cell, I need to change the cell color. my range is f12:AO87. The cell color need to change to based on one of nine items input. Example:
Yes - cell color green
Maybe - cell color blue
No - cell color yellow
Now - cell color pink
N/A - cell color purple
equal to or greater than 4 - cell color red
Holiday - cell color light blue
CB - cell color peach (orange)
CL - cell color light green

I have 03, it limits me to 3 conditional formats. I would appreciate any assistance. Thanks in advance.

I've got a spreadsheet that has multiple rows. It's setup so that the first row is a color and the second row is a different row. This pattern is then repeated every two rows. The issue I'm having is that sometimes I need to hide or delete a row. This causes the same two colors to repeat. Is there any way that I can create a formula (I'm guessing through conditional formatting) that will automatically change the color of a cell based on the color used in the row that is unhidden above it? Thanks in advance for any help.


I have read almost all subject relating to conditional formatting but sad to say I have not found a solution to what I need for my excel file to work.

I need to use conditional formatting in determining the color of another cell and apply the same color with the current cell. I am using only 2 colors, red and green.

Help please, Thank you.

I have a list of names and scores arranged in rows. I used conditional formatting and the MOD formula to color every other row, and keep them in alternating bands of color even if I delete or add a row. But, now I have 2 rows per person with the odd number rows being hidden. My formula to color every other row doesn't work anymore because when hide the odd numberes rows, all of the remaining rows are the same color. Can someone help me with this.


  A B C D E F G H 10 Johnson 27 54 76 54 76     11   h u r b b     12 Echols 86 43 28 68 21     13   b u b u r     14 Miller 76 67 23 21 59     15   r b r r r     16 Singletary 54 87 56 14 55     17   u u r u b    

Excel tables to the web >> Excel Jeanie HTML 4

HI all,
Basically what i need is a way to hide used rows if the color of the cell in column I is green( color 35 vba) but only if the cell is visible will the macro change the rows visible state, in other words already hidden rows will remain hidden regardless if they match the color requirement.

below is some pseudo code to try to explain better what I'm trying to do. The below code doesn't actually work but I was wondering if someone knew some code that would actually do what I've described.


' check the color of each cell in column I then if the color matches hide
    ' the Row but this should not unhide hidden rows that are NOT index color 35
  If Range("I2:I").Interior Then
        .ColorIndex = 35
        .Pattern = xlSolid
    Rows("I2:I").EntireRow.Hidden = True

PS DonkeyOte is a genius


I have a list created with over 1200 rows.

The list is a compilation of 3 seperate lists with duplicates.

each list is represented by its own color, (yellow, orange, green)

I want to create a check box for each color that will Hide the cells represented by that color.

Can someone give me some pointers here?

much appreciated.

Here is my issue:
I want to color multiple cells based on certain criteria. For example,
I will select all cells F1:F12 and if the percentage in each cell is
less than the percentage in the same row (different column), color that
cell red. If greater, color it yellow. If =, color it white. I used
conditional formatting, and it is screwing up the colors based on what
I'm sure is my crappy logic. Please revise if you can. Thanks,

=IF((F12>C12), 1, 0) - Formatting color chosen is yellow
=IF((F12<C12), 1, 0) - Formatting color chosen is red
=IF((F12=C12), 1, 0) - Formatting color chosen is white

But the colors are not displaying correctly for the cells where the
values are =. I can elaborate if necessary, I'm assuming its the if
loop that is the prob. Thanks,

Hi again, I'm looking for help with a code to hide each row that has a certain cell background color.

Ex. If A52's background is Orange, hide the entire row.

Orange was an example, I'm fine with the colors, but I need help determining how to identify the cell background.

This is what I've got:


Sub Hide_GrandSlam()
'Hide Empty Tech Rows
Dim row As Range
Dim Cell As Range

With ThisWorkbook.ActiveSheet

For Each row In .Range("A12:A251").Rows
If Cell.Interior.Color = RGB(63, 49, 81) Then
End If

End With
Application.ScreenUpdating = True
End Sub

Thanks in advance for any help!!!

Is it possible to hide the conditional formatting that is applied within a worksheet? (similar to hiding a macro using password protection) Not necessarily to hide the function completely, but to hide existing rules without limiting adding of new ones?

I want to take a cell range and if there is a V or .5V color that cell red, PH color green, PV color blue and CV color yellow.

range would be d3:z35. I have tired many different vba codes I found on here, I tried condition formatting and I used the Conditional Formatting website that was suggested to many folks. Obviously I'm not doing something right. Any suggestions?

I have a macro that checks to see if required cells are populated in a workbook, by looking at the interior color of the cell.

I discovered that conditional formatting was used, such that the color of the cell, as a result of the conditional format has changed.

My routine is not working as expected. So, I am now trying to figure out how to either: check the cell color that is in effect (i.e. if the cell color was changed as a result of a conditional format condition being met, I want to know the color it was changed to.) determine if a conditional format critierion was met, because then, I can just ignore the cell. Any assistance is greatly appreciated.

Is there a value I can use that keeps the information in the cell from being visible, i.e. a "clear" or no color value, or simply not displaying at all?

I do not want to hide the cell entirely, as I need to show the "fill color" of the cell, which changes depending on the value of the cell.

I have macro that changes the cell fill color to one of 7 different colors, depending on the value. (I would use the cond. formatting feature, but excel 2003 only allows 3 colors variations)

My temporary work around is to have the font display in the same color as the fill color, however when using Excel 2003 and/or lower quality color printers, you can still faintly see the value of the cell when printing.

When I use the "xlNone" constant it seems to display the value as "white" and not really "no color". I guess that could be an invalid constant, maybe.

Thanks for any information.

I have a list of code names in a column that goes on for about 15000 rows or so...I have the duplicates highlighted through conditional formatting and am in the process of creating a macro that will do various things to the cell based on the color...I can do everything i need to do except in this the cell that I want. I know how to look for and select a cell based on colorindex however this doesnt seem to work when the cell is that color because of conditional formatting. Is it even possible?..for example:

if selection.interior.colorindex=6 then

would be saying if the cell fill color is yellow whatever I want...won't work for me in conditional formatting.


I've created a named Range ("VRange") in column V16:V600

Within this range I have numerous cells with the color Red.

I would like to create a button which hides all the rows, within this range, that are non-Red color (Leaving only the Red showing)

Is there a way to create a macro without using conditional formatting?

Thanks for any help


hi to all,

here i am searching a way to define conditional formatting according to my data :

in the attached sheet i want conditional formatting like :

1 - if A1 to A20 if the Values in Minus (-) than cell color should be yellow & text color should be Red.
2 - if the values are positive so Cell color should be Green & Text color should be Black.

will be thankful if someone help me regarding this issue.


Can someone please help me with this? I can figure out conditional formatting for a cell. How can I get it for a row?

If I have data in rows A, B, C and D, and E is a yes/no, how can I change the color of the entire row? Yes is one color, no is another color. We are looking for a fill color, not a text color change.

thank you!!!

Ok... you all are probably going to think I'm crazy....

What I'm trying to do is set up a nested IF, NOT, AND statement with conditional cell formatting. I already used the Menu cell formatting for a different purpose and because I can't put in more than three conditions, I need to manually put in the conditional formatting with a formula.

In Layman's terms I'm trying to make it say the following:

IF Cell A1 does NOT equal "No" AND the DATEDIF between A2 and TODAY > 30 days, then Color row A blue.

This is what I have for the formula so far:
=IF(W2=(NOT(W2="No"))AND((DATEDIF (R2, TODAY)>30)),=COLOR A2:AZ2=(155,10,0))

Hi there - does anyone know the code to replicate conditional formatting because i am trying to have more than 3 conditions on a cell and conditional formatting only allows 3.

Is it possible to do it in VB instead?

Lets assume cell A4 is the target cell.
If value between -500 to -100 then cell pattern color change to red
If value between -100 to 0 then cell pattern color change, etc,
If value between 0 to 100 then cell pattern color change, etc
If value between 100 to 500 then cell pattern color, etc
If value = 0 then cell pattern color, etc

I would need to do this code for roughly 100 cells.
Would this cause problems to excel if its constantly looking at cell values to see if they are changing so that it can apply the new formats?

THanks in advance...

Hi. Ok. I have a tracker for our job. However there is a part of the tracker that we only need 3 months of every year. called "stars of hope"

I'd like make it so that when a checkbox is checked YES (meaning show stars of hope)..

That these two cells will show up from being hidden.

I have the formula below i'm using to hide rows.. however i cannot hide rows for these. I need all the other cells in the rows and columns to show up.

Is there a way to hide just a cell? I'd be fine with making it invisible TO.. Is it posible to remove borders, background color, and change text color.. using vba?

anyway here is what i'im using for rows. Anyway to format the cell colors/borders or hide just the cell?

Dim cb As Excel.CheckBox

Set cb = Excel.ActiveSheet.CheckBoxes(Application.Caller)
If cb.Value = xlOn Then
Rows("107:108").EntireRow.Hidden = False
Rows("107:108").EntireRow.Hidden = True
End If

say instead of hiding i just wanted to turn the cell completely white? is that easier? how to?


Hello All,

I am working on a sheet with conditional formatting. When a condition is true the cell is filled with different color to differentiate it from other. is there a way to hide the filled color while printing the sheet.