Over 1 million tech questions and answers.

Conditional Format Custom Formula Google Sheets

Q: Conditional Format Custom Formula Google Sheets

I am using conditional formatting with a custom formula in Google Sheets to fill color the minimum and maximum values in a column.

The minimum works just fine:

Code:
=$AG:$AG=min(AG:AG)
But if I change min to max nothing in the column gets highlighted.

Code:
=$AG:$AG=max(AG:AG)
The range is set to AG2:AG25

Read other answers
RELEVANCY SCORE 200
Preferred Solution: Conditional Format Custom Formula Google Sheets

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

RELEVANCY SCORE 87.6

I am trying to create a conditional format with a formula for cells n650 to n1000 that will test to see if cell N650 is blank and today is later that M650. This seems really easy but Iam having trouble with it. Below is what have so for. Can anyone help?
=AND(ISBLANK($n650),TODAY()>$m650)
 

Read other answers
RELEVANCY SCORE 86

The format is applied in Cell A2 and is based on a formula (C2+D2)>=0

I can get this to work fine for one cell, but I want to apply it down as far as cell A28 (for values in C28 and D28).

Is there an easy way to do this?

T

Read other answers
RELEVANCY SCORE 72.4

If it's possible to do this, what is the formula syntax for an if/then formula in one sheet that refers to the cell value in another sheet in the same workbook?
For example in Sheet 2, the formula would be something like, if Cell A10 in Sheet 1 is greater than 0, then the value of Cell B20 in Sheet 2 would by "Yes" but it would be "No" if the valuein Cell A10 in Sheet 1 was less than 0.
 

A:Excel formula using formula between sheets in workbook

Read other 6 answers
RELEVANCY SCORE 65.2

Hello,

I'm working on an Excel project. I need a Macro to find duplicate values in Column D and when a duplicate is found, format the font color and fill color for that row, column A thru E. Any ideas?

What I currently have finds the duplicates, but only contionally formats items in that column. I don't want it to be conditional, but here is what I'm using:

Private Sub btnDuplicates_Click()
Columns("D:.D").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("E2").Select
End Sub

Any idea how to do this?
 

A:Non-Conditional Format: Duplicates Values in 1 Column Format Fill and Font Entire Row

I got rid of the conditional formatting you recorded, because I thought the best way was to loop through the cells in the column and try to find them in the range above and below, and it found them, to format the range of cells from A:E associated with the cells. The problem with using conditional formatting is that you can't loop through cells containing a certain format, because the cell object is not actually formatted. I hope this helps...
Code:

Sub Test()
Dim cell As Range, strValue As String
For Each cell In Range("D1:D10")
strValue = cell.Value
Select Case cell.Address
Case Is = "$D$1"
If Not Range(cell.Offset(1, 0), cell.End(xlDown)).Find(strValue) Is Nothing Then GoTo Format
Case Else
If Not Range(cell.Offset(-1, 0), cell.End(xlUp)).Find(strValue) Is Nothing Or _
Not Range(cell.Offset(1, 0), cell.End(xlDown)).Find(strValue) Is Nothing Then GoTo Format
Continue:
End Select
Next

Exit Sub
Format:
' you can obviously change this to suite your needs
With Range(cell.Offset(0, -3), cell.Offset(0, 1)).Font
.Bold = True
.Size = 10
.ColorIndex = 16
End With
GoTo Continue

End Sub
[\CODE]

 

Read other 3 answers
RELEVANCY SCORE 65.2

I am setting up a spreadsheet for the year and want to use it year over year. I have the cell set up with the date (1/1/2011) with a format of d so that it displays the date as Sat, 1/2/2011 - Sun, etc. Since I want to use this year after year with minimal work and I want to highlight the cells that are equal to Sat and Sun, how would I set up that formula. I tried the basic =A1="Sat" and without quotes but it just returns #NAME. I know working with dates in formulas is different but can't figure out what I need to use.
 

A:Solved: Excel 2007: conditional format when date displayed as format d

Read other 8 answers
RELEVANCY SCORE 64.8

Hi

I am working on a workbook which contains (at the moment) 13 calculation sheets and 1 master sheet.

The 'mastersheet' contains information such as dates etc which are pulled through to the same places in all the other sheets.

I am looking for a way to hold a formula in a cell on the 'mastersheet' and have the formula, not the result of the calculation, pulled through to the other sheets.

The formula is a simple one - =c24/37*f8

Each of the calculation sheet has the figures in c24 and f8 to perform the calculation there is nothing in those cell references on the 'mastersheet' and I am getting the result 0.00 showing in the cell.

If I use =mastersheet!$c$2 on the other sheets i get the result and not the formula...

can anyone help me please... I'm sure it is something really simple but I just cannot figure it out... many thanks... Jon
 

A:Excel formula to multiple sheets...

Group the sheets you want to replicate the formulas on, then create the formulas on the active sheet and they'll "flow through" to the other sheets in the group.
 

Read other 1 answers
RELEVANCY SCORE 64.8

I have a workbook containing a master sheet (sheet 1), and several other sheets (sheets 2 to 7) which I want to populate with colour highlighted cells that automatically appear in the corresponding cells in the master sheet.
Is there a formula/function that can do this?
Thanks
 

A:Excel formula link between sheets

Read other 7 answers
RELEVANCY SCORE 64.4

Hello All,

I am needing a formula that will format a cell based on the value of another cell, this part i have managed but i am wanting to make the format of the cell change if the value of the other cell is between 2 numbers - this part i am a little stuck with, everything i have found online and tried has not worked.

I am using the conditional formatting option and then the use formula to determine the formatting.
Does this make sense? Any help would be great, cheers
 

A:Formula for Conditional Formatting

Try Cell value is between, rather than formula is

Pedro
 

Read other 3 answers
RELEVANCY SCORE 64.4

If column F equals "yes" and column H equals "Double" then I want the formula to count as 2 not 1. please see doc attached.
 

A:Conditional formula Excel

Read other 10 answers
RELEVANCY SCORE 64.4

I'm trying to improve my skill with Excel and I'm having some trouble with a difficult formula. Basically, it involves two conditions. I think it will be easier if I just write out the logic formulas I'm trying to enter. G12 and F12 are the two cells that determine the value of H12, the cell I'm having trouble with.

If G12<3 and F12<50=.2
If G12<3 and 51<F12<100=.24
If G12<3 and F12>100=.26

If G12>2 and F12<100=.2
If G12>2 and 101<F12<200=.24
If G12>2 and F12>200=.26

So I need a way of expressing all six of those conditions in one formula. This is a variable rate for a timesheet and as you can see, the rate changes depending on whether there are more than 2 people on staff (the G12 value.) I know it would be relatively simple to just enter the rates manually, but I've been wanting to learn more about how Excel works for some time, and just trying to work this out has already taught me a lot so far. At this point, however, I'm stuck.
 

A:Conditional Value Excel Formula

Read other 8 answers
RELEVANCY SCORE 64.4

pls i need help on this formula in Excel
I want to create a formula to suite a criteria that i am using to diagnose metabolic syndrome but i get stuck any time i input the formula.
this is the argument:
B2=1.4
M2=95
D2=1.74
P2=137
Q2=89
F2=10.3
if B2 is less than 1.2, D2 is greater than 1.8 and M2 is greater than 85, then we conclude positive
 

A:Solved: Conditional formula

Read other 13 answers
RELEVANCY SCORE 64.4

Hi

I have the month value on cell C2

Columns D3 is Forecast and E3 Actual for Jan-2013, F3 forecast for Feb-2013 and G3 Actual... so on and so forth.

I am trying to calculate Estimate at completion from Actual and forecast column for a particular month.
The value of month is in cell C2 (jan-2013 etc.) There are 2 columns for each month (forecast and actual)

The conditional formula should be .. when the value of C2 is Aug-2013, then EAC = sum of actuals from Column b to Col 16 (Jan-2013 to August-2013) + Forecast from Column H to Column 0 is EAC.

Please help

Thanks
 

A:Solved: Conditional Formula

Read other 15 answers
RELEVANCY SCORE 64.4

Running EXCEL in ME on a HP8260. I put a formula in a cell and it works just fine. If the result is between -25% and -100% I want to print a red XXX.

The formula in cell C9 is (A9-B9)/B9*100

How can I make it print the correct answer except if the result is between -25% and -100%? and then print RED XXX's?

I have been all over the IF function and no help.

Ideas?

Thanks,
shortcut
 

A:formula - conditional - in EXCEL

Read other 6 answers
RELEVANCY SCORE 64

I have conditional format in row 5, as follows
Condition 1
=(F5="") and dragged across
Format is for No colour
Condition 2
=ISNUMBER(FIND("G",numForm)) and dragged across
Format is for Light green if condition met

Cell Format is set at General

I have two problems
1. Cell F5 produces 4 decimal places
2. If I change Cell format to Number , with 2 decimal places Conditional format of light green does not occur.

Is there a way to have a number cell format (custom or otherwise)to two decimal places and a conditional format that leaves the light green if a number is in F5. If no number cell to remain clear.

Any suggestions appreciated.
Pedro
 

A:Solved: A Conditional Format and cell format proble,

I think you should verify that the format in the conditional format is a number too
Check if there are no quotes arounf that conditional format conditions, this happens quite often
 

Read other 2 answers
RELEVANCY SCORE 63.6

Question: Using Microsoft Excel, I need a formula in cell U2that does the following:

· IF the date in E2<=12/31/2010, return T2*0.75

· IF the date in E2>12/31/2010 but<=12/31/2011, return T2*0.5

· IF the date in E2>12/31/2011, return T2*0

I tried using the following formula, but it gives me “#VALUE!”

=IF(E2<=DATE(2010,12,31),T2*0.75),IF(AND(E2>DATE(2010,12,31),E2<=DATE(2011,12,31)),T2*0.5,T2*0)

Can someone please help? Thanks
 

A:Solved: Excel Conditional Formula

i think you just have the bracket after T2*0.75)
take that off and add a bracket at end
=IF(E2<=DATE(2010,12,31),T2*0.75,IF(AND(E2>DATE(2010,12,31),E2<=DATE(2011,12,31)),T2*0.5,T2*0))

seems to work OK
 

Read other 3 answers
RELEVANCY SCORE 63.6

Hi guys i am back
I am using 1 formula (listed below) to help me with calibration reminders but after having a discussion with my boss she would like to have the date show up in color and not wording.
=IF(DAYS360(TODAY(),S5+90)>45,S5+91,IF(DAYS360(TODAY(),S5+90)<=0,"PastDue",IF(DAYS360(TODAY(),S5+90)<45,"Due")))

She does not want to see PastDue or Due, only for the date to show up in the colors i have selected using conditional formatting.
Example: Calibration Date Calibration Due Date
8/5/2010 11/3/2010 <----- does not show up using the formula above but she would like to see the date instead.

I have =SUM(S5+92) to automatically change with a calibration date has been entered to show up in calibration due date. =IF(AF5>45, "OK", IF(AF5<=0, "OVERDUE", "DUE")) is another formula that i was playing with to help with calibration reminders. =INDIRECT("AG"&ROW())="Due" is the conditional formatting formula that i used to have the date change colors.

If there is a possible way to have this all together that would be great. Let me know what you guys think i should/can do.

Chris
 

A:Solved: Conditional Formatting/ Formula

Read other 9 answers
RELEVANCY SCORE 63.6

i had read some other posts which are related to this formula, but i still have no idea at all.

Lets say i have 2 column now, O7 represent the Result and as for P7 represent Grade.
Now what i want to do is when i enter number into O7 column, i want it to show the grade that i want at P7 column.

> 80 A+
75-79 A
70-74 B+
65-69 B
60-64 C+
55-59 C
50-54 D+
40-49 D
< 40 E

Sorry for my lousy english, hope you guys can understand what i'm trying to say
 

A:Solved: need MS Excel=IF conditional formula help pls

Read other 6 answers
RELEVANCY SCORE 63.6

I am trying to write a conditional formula using using times with the following parameters:

cell A1 7:30 AM - cell B1 5:30 PM - cell C1 =(B1-A1)*24

This formula will calculate the hours (10) between 7:30am and 5:30pm, which for the purpose of the exercise are classified as standard hours. Any hours outside these hours are classified as non-standard. I need a conditional formula (for cell C1 or D1) to calculate non-standard hours (cell D1) either side of the standard hours as required.

If anyone is wondering, my wife does childcare and I am trying to automate her daily calculations. This one has me stumped.

Thank you in advance.
 

A:Excel Conditional Formula Problem

Hi!
Something like the attached file will be fine?
 

Read other 1 answers
RELEVANCY SCORE 63.6

Despite using the information on the Microsoft website, I seem to be having problems with getting a correct conditional formula which results in a value other than true or false.

Basically in simple terms:
If value in Cell A1 is more than 19 and less than 25 I want the value 3 to go into A2.
(The formula is in cell A2)

Although I do not have a ‘not true’ value to enter other than error, the formula below does not work - or even come up with there being an error.

Formula I am using is :
=IF(AND(M2>19,M2<25),"3","error")

I have tried also putting in the values 19 and 25 in other cells on the spreadsheet and using the cell detail in the formula but even this does not work.

I am using Excel 2010. Can anyone please tell me where I am going wrong or if there would be a better function to use? Many thanks in advance!!
 

A:Solved: Help creating conditional formula

Read other 7 answers
RELEVANCY SCORE 63.6

A few months ago, I was very happy to get some good formula advice here, so I am back for more

For my spreadsheet and stock projections, I would like to achieve the following:

one column shows me the projected stock level based on previous sales -e.g. =AA776-(AS776*$Z$1)

This provides me with a future stock figure. Now I don't need this field to be populated in every row as some items are discontinued and in this case the negative outcome is just providing "noise".

So in addition to the above calculation I wonder if I can use conditional formatting based on text shown in another cell, e.g. if cell I776 shows text, then highlight cell Y776 (or even better delete contents of Y776). Is this possible?
 

A:Solved: Conditional Formatting &amp; Formula Help please

Read other 12 answers
RELEVANCY SCORE 63.6

I am trying to compare data from two columns in Excel. I would like to have excel pull the lesser valued cell and post into a 3rd column. However, if one of the cells contains 0 or NULL value I want excel to pull the only remaining value from the other compared cell. Ex. A1=200; B1=100 (I have used an IF statement inthe past for this function =IF(B1<=A1,B1*1). However if A1=200 and B1=0; I need C1 to return the value of 200 not 0. How do I do this?
 

A:Solved: Excel Conditional Formula Help

=IF(OR(B1="",B1=0),A1,MIN(A1:B1))

Welcome to the board.
 

Read other 2 answers
RELEVANCY SCORE 63.6

I am trying to find a macro formula using conditional formatting to separate names on an excel worksheet with a black line. This is a worksheet that will have new information put into it every month, approximately 600 new lines of data each time. This is data for employees, so it will have the employee name then by month, up to 12 months of data for each employee and 78 columns for each month. Each month the line will need to be changed because there is one more line of information.

The condition would be in Column E when =$E3<>$E4 (employee number)then a black line would be placed on the bottom of the cell from A:BZ, first to last column of data.

I am using Excel 2003 and I have other conditional formatting in the majority of the cells, so I can't use another conditional format from the tools menu because it will not place two in the same cell. That is why I am looking for a macro that may accomplish the same thing.

If there is another way to accomplish this that would be great. After 2 months of data I have 1200 lines of data.

I tried to attach a sample of the document to this but it would not allow me to.

Thanks,
 

A:Excel VBA Formula for Conditional Formatting

Read other 12 answers
RELEVANCY SCORE 62.8

G'Day Guys,
I'm wanting to automate a small spreadsheet I have, to put in a certain monetary value in a cell at the start of the month.

I made up a conditional formula and was able to get it to work, but of course, as soon as the next day ticks over, the formula is no longer valid, and the cell reverts to being empty.

Is there a fairly simple way to put something in place, so that once the condition is met, the value is retained?

My formula might not be ideal, and I'm happy to receive info on a better way to do this.

The conditional formula is: IF(A2=(TODAY()),A3,"") where A2 contains my trigger date, and A3 contains the Dollar value that I want to retain, once the formula gets a true condition.

Hope you can spare some time to offer advice.

Cheers Ron
 

A:Solved: Retaining data from a conditional formula XL

Read other 14 answers
RELEVANCY SCORE 62.8

Hi,

This is my second post. I have Excel 2010 and I am working with a spreadsheet for lab data that has approximately 20 tabs and each tab has 1 to 8 columns of data that represents lab results. Each column has a different heading such as Calcium, Protein, Sodium, Fat, etc. In each column is a list of data.

I am asking for help with the following:

I need to mark any data point (by underlining) that is more than 2 standard deviations from the mean. Each column has different values and criteria listed in it. Is there a way to copy and paste the conditional formating function from one column to another column much like I can copy and paste the normal calculations or statistical calculations? I am hoping that I do not have to manually run the conditional formating feature manually for each column on each spreadsheet tab.

Thank you for your help.
 

A:Conditional Formating Formula Help Excel 2010

have you tried
copy
Paste Special
Formats

It should copy the conditional format - if setup correctly

what happens when you try that

Can you upload a sample spreadsheet with dummy data
 

Read other 2 answers
RELEVANCY SCORE 62.8

Hi,

I need help with a formula for an excel spreadsheet.

If the contents of a particular cell (in my example r1) = 0, than delete the entire line (in my example it would be line 1)
Running XP, using MS Office 2007

Thanks in advance.
 

A:Solved: Excel FOrmula conditional delete

Read other 7 answers
RELEVANCY SCORE 62.8

Good afternoon,

First and foremost, I have over 3000 cells that I need to apply this conditional format to, so if possible I'd like to avoid having to manually update each formula.

Bascially I have a conditional format set up so when the value in cell A="no" or A="n" then all the cell rows that correspond will automatically turn red. I've managed to get this far with no issues. However, I now have to copy this conditional format to the remaining 3000 cells below. I've found that simply copying and pasting the cells does not work becaue the formula is not auto updating so all the cells below are still referencing the inital cell A in my formula.

My Question: Is there a way to copy a conditional format and apply it to cells and have it auto update the referenced cell?

Here is a screen shot to better illustrate my dilema.

Here what my workbook is currently looking like when I simply copy the cells and paste them into cells below. The conditional format formula is still referencing cell A3 and reads as =OR($A$3="no",$A$3="n") for the second section even though it should reference =OR($A$16="no",$A$16="n").

 

A:Conditional Formatting with Formula in Excel 2007

Read other 8 answers
RELEVANCY SCORE 62.8

Columns = A=Cost B=MAP C=Code D=Price

I need to calculate the value of a formula with different values. Let me try and explain it like this.

In Column D
=IF(A1<=25,A1*1.4) ok, I get this part but how do I get the same calc for different values?
=IF(A1<=50,A1*1.3)
=IF(A1<=100,A1*1.2)
=IF(A1>100,A1*1.1)

How do I combine all these argumants into one formula? Could use a lookup table so I can change the values without re-doing all the formulas.

Then to confuse the whole matter I need to add the following condition with the HIGHEST priority so if this statement is True, then ignore the rest.
=IF(C1=1,B1)

I never was real good at creating Logic statements. I understand the concept, but putting it all together is another story.

JOhn ><>
 

A:EXCEL: Conditional Formula with a range of variables

Read other 14 answers
RELEVANCY SCORE 62.8

i´m having trouble using formulas in excel. I need to count values. I could do it using "countif" for a single value, but i need to count using a condition... Follows an example:

A B
X 2
Y 1
X 2
Y 2

I need to count if the values in the column A =Y and the values in the column B = 2.
 

A:Problems using count and conditional formula in Excel

Read other 7 answers
RELEVANCY SCORE 62.4

Hi,

I am having trouble getting results from a formula that is trying to return the sum of numerous values in a field once 2 conditions are met. I would say I have between novice and intermediate experience with excel formulas so I hope I can explain it clearly.

Worksheet 1: results

Worksheet 2: data
Column 1 (month): April, April, April, May, June, June, July, July, etc...
Column 2 (code): 4000, 6500, 4902, 4902, 3002, 4000, etc...
Column 3 (amount): £0, £0, £3598, £725, £0, £2212, £0, etc...

I want to get the sum of the amounts in Column 3 for the code in Column 2 in a specific month. For example, how much was spent in April for code 4902?

I am working with this formula
=SUM(IF(Worksheet2!$A$6:$A$245="April",IF(Worksheet2!$B$6:$B$246=4902,Worksheet2!$C$6:$C$246,0),0))

However, this is returning errors.

Can anyone help solve this one, perhaps with a simpler formula?

Thanks
 

A:Solved: Excel 2007 - Conditional SUM formula problem

Read other 16 answers
RELEVANCY SCORE 62.4

Hi there Guys,

I need some help with Excel 2003. I cant post and example up as it is confidential information. But I want explain what I need the best I can.

I have a spreadsheet with 8 Columns (A to H). The columns I am most concerned about are Column E and Column H.

In column H, I have applied the following conditional formatting:

If Cell Value is equal to 3, the cell should be filled in red.
If Cell Value is equal to 4, the cell should be filled in orange.
If Cell Value is greater then or equal to 5, the cell should be filled in green.

In column E I have numbers and whatever I enter there is automatically added together and the total is displayed in a different cell.

I got all that right, but now I want to do this.

If the a cell is red (or 3) in Column H I want all the corresponding line numbers in Column E to be added together and displayed in a different cell. I want the same rule to apply a cell in orange (4) and a cell in green (5 or greater). Can this be done. If I get this to work then the rest will be easy.

Can anyone help please??
 

A:Solved: Help with Conditional Formatting & Formula's in Excel 2003

Read other 14 answers
RELEVANCY SCORE 62.4

I have a big cost sheet so each line is linked to other books

the thing is my totals dont match

i cant just conditional format duplicates because its comparing the formula (link) result

can i conditional format to highlight the same formulas

or any other way informing of duplicate formulas without editing - maybe copying tho.
 

A:Solved: XL07 Conditional Formatting - Duplicate formula

To just compare formulas rather than their result you could use

Code:
=formulatext(firstcell)=formulatext(secondcell)
Trouble is, you'd need something more elaborate to check for duplicates worksheet wide
If you know what your formulas are that are likely duplicates, you could do a find all in find and replace
syntax for searching for whole formula would be:

Code:
~=A2*B2
- you can substitute the formula bit for your formula
You could also do it by eye by going to the formulas tab and selecting "Show Formulas"
Failing that it would be VBA or jiggery pokery with using a second sheet and =formulatext to refer to your original formulas combined with conditional formatting to highlight the dupes ...
http://chandoo.org/wp/2012/08/09/formula-forensics-no-026/
 

Read other 2 answers
RELEVANCY SCORE 59.2

It's deceptively simple what I'm trying to do, but after hours puzzling over Excel VBA forums and tutorials, I'm ready to throw in the towel. I'm using Excel:MAC 2011.

Each row in my sheet represents a year in the life of one job. What I'd like to be able to do is enter an employee's name in column C, and start and end dates in columns F and G. Then I want the script to dynamically pick up the background color for that employee (column D) and copy it into the appropriate start-end range in the calendar in columns I-NJ (autofilled with 2012 dates).

Background colors for each employee are currently set in a key, and I have a script that lifts the appropriate color to each row of data (in column D). This part of the script actually works.
Code:
Set oName = Range("C6:C500")
For Each Cell In oName
Select Case Cell.Value
Case "Lynnette"
Cell.Offset(0, 1).Interior.Color = [NM7].Interior.Color
Case "Cheryl"
Cell.Offset(0, 1).Interior.Color = [NM11].Interior.Color
Case "John"
Cell.Offset(0, 1).Interior.Color = [NM9].Interior.Color
Case "Michelle"
Cell.Offset(0, 1).Interior.Color = [NM12].Interior.Color
Case "Richard"
Cell.Offset(0, 1).Interior.Color = [NM8].Interior.Color
Case "Clara"
Cell.Offset(0, 1).In... Read more

A:Excel VBA - modified gantt chart with custom conditional formatting

Read other 11 answers
RELEVANCY SCORE 59.2

I want to set up some condtional formats based on different cells

so -

i have 8 columns
A1 - B1 - C1 - D1 - E1 - F1 - G1 - H1
control - date - control - date - control - date - control - date

so if A1 is blank I want the fill in a1 through to H1 to be red
when A1 has an entry I want (B1 auto has a value)
C1 - H1 to be red

when C1 has an entry I want (D1 auto has a value)
E1 - H1 to be red

when E1 has an entry I want (F1 auto has a value)
G1 - H1 to be red

When G1 has an entry I want all A1 - H1 to be green.

BTW I have also asked a question about dates see here http://forums.techguy.org/t247849.html
 

A:conditional format

Read other 8 answers
RELEVANCY SCORE 59.2

Not sure how to do this one.

We have a spreadsheet at work to track unique part numbers. These parts eventually wear out but can be reworked and reinstalled again. 3 reworks is the cut-off meaning once installed the 3rd time I'd like it flagged to warn the operator. I'm not sure if countif or conditional formatting or both should be used.

I've attached the file. The "Number" column has the part numbers we are tracking. ​ Any help would be greatly appreciated !!​
 

A:Conditional format

Read other 16 answers
RELEVANCY SCORE 58.4

Hi,
I'm trying to have the format in selected cells change color when a certain cell has today's date in it, and stay changed.
I highlighted the cells that I want to change, then I went to Conditional Formatting and made a new rule
HTML:
=b2=<now()
It works fine without the less than symbol

what am I doing wrong?

Mike
 

A:Solved: Conditional Format

Read other 6 answers
RELEVANCY SCORE 58.4

Hi, I know how to do conditional format for distant cells, but the problem is I need more than 3 conditional format options I dont want to play with vbs. I need to know how to add many values to a condition formula.

im pretty sure i am not clear so here's an exemple!

if the cells in the column g is = to either "15 min break ?" or "lunch break?" the cell on the row where the "15 min break ?" or "lunch break?" need to be formated a specific way.

if the cell in the column g is = to offline duties or anything else it need to be formated an other specific way.

ive been able to have the formula to work with 1 source like 15 min break in condition 1 and on condition 2 the lunch break. i also been able to get the formula to look at a list but it take the info from the fisrt cell in the list and ignoring the rest of the list.

can anyone help me!!!
thanks
 

Read other answers
RELEVANCY SCORE 58.4

I would like to have cells with a value of zero, hide the zero, and my plan was to do a conditional format to make the font no color if the cell value = 0. That wouldn't work. The zero remained in black. So, I tried to have the background color be white to hide the zero, but that didn't work either.

The reason I've got this zero showing is that I'm using vlookup to fill the cell. The formula is as follows:

=IF(ISNA(VLOOKUP($D$1,$A$127:E141,5,FALSE)),"0",VLOOKUP($D$1,$A$127:E141,5,FALSE))
I need to change the cell value from #N/A to zero because the cell is part of a column of numbers that I need to add so I need everything to be numbers.

While the worksheet works fine with the zeros showing, it's not consistent with the way the rest of the sheet looks, and I want it to look good, so if someone can tell me how to fix this I'd really appreciate it.

Also, if it makes any difference, I've got the same =if(isna(vlookup ... statement as data validation for the cell to ensure that no one can type anything in there--I want only data from the vlookup input into the cell.

Thanks for any and all suggestions.
 

A:Can't conditional format when cell value=0

Read other 6 answers
RELEVANCY SCORE 58.4

Is there a short and painless way of formating all excel sheets belonging to a workbook?
I would like that every sheet will have a footer with page number and sheet name.
Also is it possible to creat something similar to an index in word?
Thanks a lot for your help
Sibylle
 

Read other answers
RELEVANCY SCORE 57.6

Hi

I’m trying to keep a running total of bill payments.

I’m using conditional format to change the color of the payment to red
on the day it’s due and keep it that color till the end of the month
Each month I will start a new sheet

In module 1, I have the following code:

HTML:
Function ColorSum(ColorCell As Range, SumRange As Range) As Variant
Application.Volatile
Dim Cell As Range
For Each Cell In SumRange
If Cell.Font.ColorIndex = ColorCell.Font.ColorIndex Then
ColorSum = Cell + ColorSum
End If
Next
ColorSum = ColorSum
End Function

In the total cell I have
HTML:
=colorsum(C1,B5:B30)
Where C1 is the reference color (red)
Since colorsum won't recognize colors that were made with conditional formatting
Is there a different formula I can use that will recognize the change of color based on conditional format?

I’m using

HTML:
=day(today())>=C5
For conditional format where row C is the due date and the conditional format formula is in B:5
In B6 it’s
HTML:
=day(today())>=C6
and so on

mike
 

A:Solved: colorsum and conditional format

Read other 6 answers
RELEVANCY SCORE 57.6

Hi,
I know how I can use Conditional Format to change the color of a number in a cell depending on a date, but how do I keep the new color from changing back with out using something like a macro to “paste Special” the cell before save and close
Mike
 

A:Solved: keep the color after conditional format changes it

Read other 16 answers
RELEVANCY SCORE 57.6

OK - Before anyone starts to yell about multiple posts, I have searched and viewed all of the posts prior to mine yet I run into the same issue each time.

Here's what I'm looking to do:

IF a cell in Column BK is blank AND a cell in Column N is greater than 4, highlight red.

The Formula I currently have is:
=($BK2="")*($N2>3)

There are two things that are happening:

1) Cells are being highlighted red even though they have a value in Column BK (formula above conditions BK to be blank).
2) Cells are being highlighted red even though they have a value in Column N less than 4 (formula above conditions N to be greater than 3).

Please let me know if you require any additional information. I thank you in advance for your assistance.
 

A:Solved: Conditional Format with Two Conditions

Hi
Although your formula works I prefer a more straightforward approach. Assuming data is in rows 2 to 20 this would be your conditional statement;

=AND($N2>4,$BK2="")

You don't say which cells you want to shade. If it is only cols N and BK you'd put this in the "Applies to" box;

=$N$2:$N$20,$BK$2:$BK$20

If you want to shade everything from N to BK you'd enter this

=$N$2:$BK$20
The important point here, and probably the reason your condition is not working, is that the Applies to and the conditional statement have to be consistent i.e. if your Condition statement cites Row 2 as above then your Applies to must start in Row 2 as well.
 

Read other 2 answers
RELEVANCY SCORE 57.6

Is it possible to add conditional formatting to a text box within an Access report.

I want to be able to highlight certain records to the user to highlight the urgency of the item.... e.g. bold and italic formatting.

Does anyone know whether this can be done?
 

A:Access 97 Reports - Conditional Format

Hi, welcome to TSG.

Yes, this can be done, using the On Format event of the report. This link gives you an example of the code to do it.

There is also a conditional-formatting feature built in to 2000 and 2002.
 

Read other 1 answers
RELEVANCY SCORE 57.6

I am using conditional formatting to look up from list and highlight occurrences in column I. I am using
=IF(ISERROR(VLOOKUP($I2, Sheet1!$A:$A, 1, 0)), 0, 1) or
=MATCH($I2,Sheet1!A:A,0)>0 . Both only highlight the first instance in the column. How would I get it to highlight the first and second instances for each on the list. I tried with offset but not good with offset. Any help is appreciated.
 

A:Conditional Format only highlights first occurence

Read other 6 answers
RELEVANCY SCORE 57.2

This file is from Excel 2007
This is about Conditional Formatting Help
Test file is attached

My question is how can i make row three work for the rest of the rows?

A3:A10000 is a number from 1-5
B:M is just ramdom information that i will be Pasting in

Explination:

A3 has a number from 1-5. Cell has CF so that if 1-5 is entered that number will be a certain color.
Once number is in A3 the other cells B3:M3 will change that same color.

Now if i put any of the numbers 1-5 the cells B3:M3 will change what ever color number is in A3.

With that being said if i take and highlight B3:M3 and take the little small box down at the bottom right hand of the last cell, click and drag down to duplicate the CF, all the cells now refer to A3.

I would like for Each row from A3:A10000 to be able to put the number 1-5 and all the cells with that row B:M to change to the same color.

I have row 3 set up like i want. For example just change the number in A3 from 1 through 5 and you will see what im talking about.

Now i can go in and change the CF for each row but i dont have that kinda time to go from A3:A10000

If anyone can help, Thanks in advance.
 

A:Solved: Conditional Format, Excel 2007 Help

Read other 6 answers
RELEVANCY SCORE 57.2

I need to conditionally format a cell based on the outcome of a formula and the value in another cell and I&#8217;m stuck as to how to do it.

I have a table that has the next 50 weeks of our project across the top row (row 10) and subsequent "week ending" date in the next row (row 11) and two columns (columns I and J) that I enter the start and end dates in respectively (always a 5 day working week whose end date corresponds to that stated in row 11).

For each row I have used conditional formatting based on the function (=AND(L$11>=$I14,L$11<=$J14)=TRUE) to highlight the cell on row 14 for which the dates I enter (I14 and J14) fall within the given week of the project based on the ending date of each week (row 11).

This has worked perfectly as a scheduling tool up till now however I have been asked to change it. I now require that the formatted colour be one colour when the value in a separate column (column F) is 1 and another colour when the value in the column F is 2. Any help would be greatly appreciated.

I have attached the document to help if needed and please post if it's not able to be done please.

Thanks in advance...
 

A:Solved: Conditional formatting - Two conditions... One format

Read other 15 answers