Free Microsoft Excel 2013 Quick Reference

Conditional format if cell=0 then font colour same as background .

I have linked a cell from another sheet. If the original cell is blank, 0
shows in the linked cell (text would be typed in source cell).

To handle this, I conditionally formatted the linked cell, so if it equals
zero, the font colour is the same as the background colour of the cell so you
can't see the zero

But the zero still prints even though it is not showing prior to printing


Hi I would like to copy cell B3 from sheet2 into cell D3 in sheet1 if cell A3 in sheet1 is same as A3 in sheet2.
Can you please help?

sheet1 looks as folows
nameA ll scoreA ll
nameB ll scoreC ll
nameC ll scoreR ll
etc.

sheet2 looks as folows
nameA ll scoreB ll
nameT ll scoreZ ll
nameB ll scoreH ll
etc.

What I need to do is to get
sheet3
nameA ll scoreA ll scoreB
nameB ll scoreC ll scoreH

not to include if collum b or c empty
example:
nameC ll scoreR ll
nameT ll ll scoreZ

Thank you

I need to conditionally format a cell if it is less than or equal to the value in the cell below it . However, if there is an "NA" in the cell below it, then the cell is formatted - as if the "NA" is a 0 (and thinking the formatted cell is less than the cell below it.) How do I set up the conditional format to format the cell if the cell below it is less than or equal, but "NA" (or any other type of text)?

Thanks.

Hey all,
I am trying to have conditional formatting of cells apply only if another cell is blank in 2007.

A3 has a formatting of =MOD(ROW(),2)=1, then =AND(A3<>"",A3+15<=$G$1) to change the color based on date and finally =AND(A3<>"",A3+30<=$G$1) to again change the color again by date.

What I would like to do is for the cells in columb A to change only if corresponding columb B cell is empty, without using macros if possible.

Dear All, On clicking a check box a macro runs with code stored in a vba module. I need to be able to exit the macro if cells of a certain colour are detected in a defined range of cells on the Active sheet. A single colour is involved but it results from numerous conditional formatting formulae (alternatively a unique font colour could be used).

Any help greatly appreciated. Many thanks

I have a totals spreadsheet that links many other spreadsheets. I need to set up a formula for conditional formatting if the cell is blank (NOT ZERO). I need to ensure that the users are inputting zeros and not leaving the cells blank.

I tried this but it's not working: ="IF('[Brown Deer-Q2-08.xls]Week 7'!G13)

If the cell value is equal to today's date, then it should appear red as part
of the conditional formatting.

If D3=today(), then red/bold.

For insurance policies that will expire in the future, I would like a
formula to let me know when that date has arrived.

The cell date is that expiration date, so if I change that date to test my
formula, I find that it did not work.

What would be the best way to assign the formula?

Thanks in advance for your help.

Hello,

I am trying to use the if statement:

If cell = 0 then

end if

however, this notation also picks cells which are blank... how can I fix this?

thanks
andy

I have a sheet that uses vlookup when the lookup returns #na error how can i conditional format these cells to so text is same as background
Thanks
Tim

I have created a spreadsheet that is used to create orders. Relatively unskilled users enter box level information into an entry sheet which generates a bill of materials (BOM). There are both one to one relationships and one to many relationships between the entry sheet and the BOM. Selecting any two specific items together may also alter the resulting BOM. The BOM contains about 100 distinct items. In general only a small percentage of the items in the BOM are actually used in a particular project.

I would like to implement a conditional formatting function something like
ˇ§if contents of cell = 0 then row height = 0ˇ¨
to hide the whole row.

This would result in a list of required items rather than a sparsely populated list of predominantly zero quantity items.

Data filtering is one solution, however, there are some problems.
„h The BOM is a protected sheet so data filters do not work while the protection is enabled.
„h Data filters do not update if the source data is change unless the filter is re-selected.

A macro that unprotects the BOM sheet, applies a data filter then resets the password is a possible solution but this would leave the spreadsheet password visible if the macro crashed. Also the user would need to run the macro each time an item is added. A macro triggered by select the BOM sheet would be acceptable but is this possible?

Is there a better way to achieve this result? If not I would appreciate some guidance on the code needed to achieve this objective.

Thanks David from NZ

Hi all

I am trying to insert a conditional format formula so that if there is no entry in a particular cell in column D then the relevant row format is font red and bold.

i.e if D3 is empty or 0 then row 3:3 has the above format.

I have tried inserting this CF formula =D3=0 then applying the format but not working.

Please help.

Thanks

Si

Hello,

I have simple macro mentioned below. It works perfectly if Cell "D1" is set to Red Fornt Colour manually. But if Cell D1 gets Font Colour as Red due to conditional formatting, then this macro does not work.

Please help me.

Sub msg()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("D1").Select
If Selection.Font.ColorIndex = 3 Then
msgbox "RED!"
Else
msgbox "NO RED!"
End If
End Sub


I was wondering if someone might be able to help me out with something I can't manage to get my head round. I'm not sure if this is the right to be posting this thread - I've a feeling though that the problem may only be able to be solved using VBA.
I'm creating a work scheduler in Excel 2004 - 2 linked sheets covering half a year each with a month overlap from the first sheet to the second sheet.
The formatting of the diary elements all hang off the cell containing the first date - i.e. cell A3 = 01/01/2006.
What I'd like to do do is get all the cells in the rows directly below each date cell to look at cell A3 to get their formatting. At the moment I have the following condition formatting to run against each cell within the schedule:

Condition 1:
then font colour = X and pattern colour = Y
Condition 2:
then font colour = X and pattern colour = Z
Condition 3:
then font colour = W and pattern colour = none

I can copy the conditional formatting horizontally without any problem but when I try to copy vertically it screws up as the next cell down in the schedule is trying to get its formatting from the cell A4 which has no date and results in no formatting
Bascially I need each of the cells in a column to reference the one header cell (all column A cells to reference cell A3 for example). It would be great if the whole of the formatting for all cells could hang off the first date cell - but I expect that is asking too much.

Hello,

I'm trying to set up a spreadsheet with more than 3 conditions in conditional formatting. The target cells have If formulas, the values of which need to change colors and bold if true. Here is the code that I have so far:


	VB:
	
 Range) 
     
    If Intersect(Range, [A1:A100]) Is Nothing Then Exit Sub 
     
    Application.EnableEvents = False 
    With Target 
        If .Value = "AP" Then 
            .Font.ColorIndex = 15 
            .Font.Bold = True 
        End If 
        If .Value = "F - Avoidable" Then 
            .Font.ColorIndex = 6 
            .Font.Bold = True 
        End If 
        If .Value = "F - Unavoidable" Then 
            .Font.ColorIndex = 45 
            .Font.Bold = True 
        End If 
        If .Value = "L" Then 
            .Font.ColorIndex = 42 
            .Font.Bold = True 
        End If 
        If .Value = "ON" Then 
            .Font.ColorIndex = 37 
            .Font.Bold = True 
        End If 
    Else 
        .Font.ColorIndex = xlNone 
        .Font.Bold = False 
    End If 
End With 
Application.EnableEvents = True 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'm not that experienced with VBE and have been trying to learn it on the fly. Can someone take a look at this and let me know what I've done wrong?

Thanks!

HI guys

It's been a while. Is it possible to conditional format a cell which is offset from the one you are currently entering? i.e if A1 is being entered, depending on what value is in it, B1 then turns green? If it is possible is it then possible to colour a range of cells in the same fashion please?

I have formulas in cells C42:C47. I need a conditional format that will show a zero or leave cell the blank if there is no number in the cell.

As always thank you for any help!
Sheila

Hi all.

My problem is i have a range B1:B10 with conditional formating, depending on the value of A1 cell. IF A1 = 1 then cells color in range will be draw in white; if A1 = 0 then range will be formated to black color.

But i also want that the cells be deactivated for the people don't can write nothing when the conditional formating is black; and the cells deactivated when the cells is white.

I hope someone can help me, I appreciate to much.

Thanks

Jorge

I would like to create a conditional format based on the content of the cell,
rather than the value.

I have a spreadsheet that has empty cells, cells with values of 1 and 2, and
cells that will have values of 0 based on 1-1 or 2-2. I do not want to
highlight the blank cells, I only want to highlight the cells that contain a
formula, even if the formula's value is 0. How can I do that? "Cell Value"
looks at the result of the formula and highlights all blank cells. I cannot
figure out what formula to use to look at the cell content rather than the
cell value.

For example:

Cell A1 is blank -- no highlight
Cell A2 value is 1 -- no highlight
Cell A3 value is 0 because formula is =1-1 -- highlight.

I only want to highlight the last cell because is contains a formula.

Thanks in advance!

Hi,

I want to apply conditional formatting if any value will appear in the cell.
The cell has got a formula linked to another sheet, so if the other sheet
will get a value, then this is picked up in the cell, at that point, I'd like
to have the cell to change color.

Thank you
Alex

i have a report i want to use some conditional formatting on the report, if
the value in the elapsed column "I" has a value that is greater than 4 i need
the background colour to be red and have white text, nnow that part i know
how to do, where it gets complicated is i also need it that if the value in
column "J" (Estimate Date) has a value that is greater than (Empty) then the
red background and white text formatting is to be enullified. how can i do
this?
--
Stuart

I would like to create a conditional format based on the content of the cell,
rather than the value.

I have a spreadsheet that has empty cells, cells with values of 1 and 2, and
cells that will have values of 0 based on 1-1 or 2-2. I do not want to
highlight the blank cells, I only want to highlight the cells that contain a
formula, even if the formula's value is 0. How can I do that? "Cell Value"
looks at the result of the formula and highlights all blank cells. I cannot
figure out what formula to use to look at the cell content rather than the
cell value.

For example:

Cell A1 is blank -- no highlight
Cell A2 value is 1 -- no highlight
Cell A3 value is 0 because formula is =1-1 -- highlight.

I only want to highlight the last cell because is contains a formula.

Thanks in advance!

Hi guys

Is the above possible? I have a spreadsheet which contains forecasted work volumes for future weeks, which are then overtyped week by week with actual values.

I was wondering if there was a way to use conditional formatting to change the font color once the formulae were overtyped with values.

Cheers

BB

Hi,

I have a macro that inserts a reference to a named range elsewhere in the workbook into a particular cell.

For example, say cell A1 is a range named FIRSTCELL and in this cell is the value 10.

When I run the macro, it enters the following formula into cell B1: =FIRSTCELL
So cell B1 is now returning the value 10.

What I want is to conditionally format cell B1 so that if it has the formula =FIRSTCELL it turns a particular colour BUT I don't want it to change colour if there is any other formula entered. For example if the user entered =1+1 into cell B1 I don't want the cell to change colour.

Thanks in advance for nay help
Gareth

Hi.

Is there any way to apply conditional formatting to any cell that has any data input? Basically, we have a spreadsheet with multiple worksheets and, for no reason other than pure aesthetics, would like any cell that has data in it to have a border. This is for a report that is published every day and has both text and numbers. We could manually format the cells, but it would be really handy if it could just be automated.

Thanks in advance.

I need help with a formula for conditional formatting.

if a1 > 0 AND a38 = 1 then format cell background solid red
if a1 > 0 AND a38 = 0 then format cell background green

I'm having problems integrating the AND.