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

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.)

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)

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

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.

How do I Open Multiple Saved Work Sheets at the same time, so that I can find duplicates or edit info/

Thank You,

Gary

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?

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]

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.

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

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.

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

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

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

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

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.

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.

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

Try Cell value is between, rather than formula is

Pedro

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

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

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?

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.

Hi!

Something like the attached file will be fine?

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!!

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

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

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

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?

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

Welcome to the board.

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

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,

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 ><>

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").

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.

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

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.

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

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.

I have a pivot table that I wan to Grey out certain cells.

Example Columns I, J, K, - I want to be grayed out based on the data in column G.

So if Column G is blank gray out the other columns. Now this works for cell for cell. so if G5 is blank then I5, J5, K5 will be grayed out.

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

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??

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.

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/

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

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 !!

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

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

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

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.

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

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

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.

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?

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.

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.

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.

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