Conditional Formatting Conflict
Apr 10, 2009
I think I have a problem with a conditional formatting conflict. I have 2650 rows and columns A - H. The rows are alternately shaded using =MOD. In column "A" I will enter serial numbers. I formatted column "A" to showed any repeated serial numbers in red with =COUNTIF($A$3:$A$2650,A3)>1. It works with one problem. Any repeated serial number in a shaded cell appears as black. I assume there is a conflict between the two formulas. I have tried switching them, but nothing I do will make the serial number in the shaded cells appear as red.
View 3 Replies
ADVERTISEMENT
Oct 19, 2006
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
View 7 Replies
View Related
Sep 11, 2009
I've set up a list table using format hh:mm, ranging from 00:00 to 00:10. And set up conditional format, ie "less than or equal to" =a1, format red, Say a1 is 00:05 and works fine. Highlights all cells equal or less than 00:05. But when I add a second condition, greater than or equal to same cell, it does not work. I'm guessing it's a conflict it does not like. Not sure if there is an actual calculation that would work better within conditional format rather than the built in options ?.
View 3 Replies
View Related
Sep 16, 2013
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
View 2 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Feb 15, 2010
I have the below two codes in my worksheet, the first one is run using
a button and the second one is an automatic macro to enter fixed
dates. There seems to be a conflict, as each time I use the button to add a
row, I get an error, and when I go to debug it highlights this row in
the second macro --> If Target.Value = "" Then. Is there a solution? I can't see why there is an error, as both are
separate codes?
View 2 Replies
View Related
Feb 13, 2007
I have a workbook that multiple users access (read only) to do quick estimates.
This workbook has a "Print & Record" button/macro that does just that... it prints two copies of the estimate, then records/copies the basic data to another workbook (Retail Estimates Data.xls). The macro opens, pastes to, and closes this file in a fairly quick time frame, but it is conceivable that two (or more) users could be trying to do this at the same time.
What I need is some code to check if the Data file is open by another (read only status?), and if so give a message to that effect, and ask to try again, by which time the other user instance should have finished with the file.
View 5 Replies
View Related
May 5, 2009
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
View 5 Replies
View Related
Jul 5, 2013
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies
View Related
Jan 29, 2013
This is my code:
=IFERROR(IF($B$41="","",HLOOKUP($B$41,InsulationH,(Mark+1))*$C$41),"")
If value of $B$41 is "A1", it does not execute
In all other cases it works fine ("B1", "C1", "D2"...)
Apparently, it's a cell name conflict with macro code
View 2 Replies
View Related
Feb 10, 2014
I have a simple form built, but the data validation is only partially working. I've attached a sample workbook.
basically, when a user tries to put in a letter in the GP interaction field rather than a number, it prompts user to re-enter. This part is working, except when the submit button has been clicked, the field clears. the field clears, it prompts the user to re-enter.
form based input - Copy.xlsm‎
View 3 Replies
View Related
Dec 3, 2009
With the kind help of this forum, I put together a nice business tool that has a fair amount of automation via macro buttons, etc. It works great in XL 2007 but some users are having issues in 2003. I appreciate this is quite general but are there significant conflicts / differences with coding in these two different versions?
View 6 Replies
View Related
Feb 1, 2007
I recently launched a model that uses filtering on protected worksheets. The model was developed in Excel 2000, and everything works perfectly for the users who are also on 2000, but my Excel 2003 users get an error on opening the workbook.
The error asks for the password to unlock the sheet and is difficult to clear. Users have to hit Cancel 8 times to clear the error, at which point, the model appears to function perfectly. (There are 12 worksheets in the model, 2 with filters, all locked and password protected.)
The follwoing code is in the 'ThisWorkbook' module:
Private Sub Workbook_Open()
With Worksheets("Initiatives")
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Because the error occurs when first opening the model but not at other times, I'm thinking that there's something in the Workbook_Open procedure that is causing the error.
View 9 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Dec 9, 2008
In a column, there are 4 option for each cell -
"PAYMENT DUE"
"PAID"
"UNDERPAID BY..."
'OVERPAID BY..."
These are filled in by a formula
I would like to make them 4 different colours but excel only allows 3 options
View 14 Replies
View Related
Mar 5, 2009
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
View 2 Replies
View Related
Dec 23, 2009
I seem to have a problem using conditional formatting with Vba in Excel 2003
When I run -
View 3 Replies
View Related
Nov 11, 2009
Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?
View 2 Replies
View Related
Feb 3, 2010
I have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"
In "book2" where I am getting stuck is as follows,
Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??
What I need is to repeat the conditional format over and over again forever is there any code for this I can use?
View 9 Replies
View Related
Oct 31, 2008
I have used conditional formatting various ways to format an individual cell. I was wondering does anyone know if it is possible to conditionally format a row? Eaxmple: If D7=Manager, then format A7 through Z7. If so, how would I go about setting that up?
View 3 Replies
View Related
Dec 15, 2009
I'm wanting to create a formula in column B that will return a value of 10 if column A is any value between 70% -100%, will return a value of 5 if column A is any value between 50% - 69%, and will return a value of 1 if column A is any value between 1% - 49%. Can you help me?
View 6 Replies
View Related
Oct 29, 2013
I want to do an if (I think I do??) command so that if a1=b1, then the cell turns blue and if a1=0, the cell turns red, with the false statement just being blank.
View 3 Replies
View Related
Apr 21, 2014
I have data in column B.
And if the below mentioned values are present in column "B" then the cell colour should change to "RED Colour"
Bombay
Mumbai
Bangalore
if any other values other than the above mentioned then the cell format should remain as is.
View 1 Replies
View Related
Jun 11, 2008
I have my formulas in place and they are working. The reason why some are coming up with #DIV/0 is because I do not have the data to complete the calculation.
Is there a way to make the #DIV/0 error go away until I imput the data to complete the calculation? I was thinking there might be a way to do it with conditional formatting.
View 9 Replies
View Related
Jun 30, 2008
Sub Macro1()
With Range("A1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1"
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=A1B1"
.FormatConditions(2).Interior.ColorIndex = 50
.AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault
End With
End Sub
If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5
Condition 1 is
=IT65533=IU65533
Condition 2 is
=IT65533IU65533
But if I run the code with A1 as my active cell, it gets entered correctly as
Condition 1
=A1=B1
Condition 2
=A1B1
View 9 Replies
View Related
Jul 18, 2008
I would like to add conditional formatting for the whole row based on whether the value of cells in column A is "CON".
For Each c In r
If c.Value = "CON" Then
c.EntireRow.FormatConditions.Delete
c.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:=c.Address = "OK"
c.EntireRow.FormatConditions(1).Interior.ColorIndex = 35
end if
next c
the "CON" is a formula and can later take the value "OK".
The conditional formatting should highlight the whole row as green when that happens.
View 9 Replies
View Related
Feb 25, 2009
I am not sure if this requires conditional formatting/vba function
If I enter something in cell A1 and leave blank in cell c1, in cell c1 it should print the message "Required Entry, Please Fill up". and this condition should be applied to entire column, where ever in column A there is some values and column c is empty this condition should apply.
View 9 Replies
View Related
Apr 27, 2006
I don’t know if it is possible in Excel 2000 but I have a simple IF formula in a cell that gives "" on certain conditions.The problem I have is that in another cell I apply conditional formatting – green >100%, red<100% e.g.
Is it possible to have a third conditional format that gives a blank if the cell is ""?
At the moment it just turns out green as it doesn’t seem to recognise the cell as actually been blank as it has a formula within it.I have tried ISBLANK etc.
View 3 Replies
View Related
Jun 30, 2006
Conditional Formatting > 3
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim cr As Date
cr = Today()
If Not Intersect(Target, Range("C5:M44")) Is Nothing Then
Select Case Target
Case "X"
icolor = 10
Case "N/A"
icolor = 2
Case cr.Value >= Today()
icolor = 6
Case cr.Value <= Today()
icolor = 3
Case "DATE?"
icolor = 8
Case Else
icolor = 2
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 7 Replies
View Related
Oct 12, 2006
I've written a macro that copies a range of cells (size varies) from Sheet1 to Sheet2, deletes the conditional formatting already there, and applies fresh conditional formatting to Sheet2 on the new range. But, the expression in the FormatConditions is way different than what I specified in the code. Here's my Conditional Formatting
Set mt1 = tData.Columns(1). Find(What:="", After:=tData.Range("A1"), LookIn:=xlValues)
tData.Range("A2:A" & mt1.Row - 1).FormatConditions.Delete
With tData.Range("A2:A3" & mt1.Row - 1).FormatConditions _
.Add(xlExpression, , "=A2=A1")
.Font.ColorIndex = 2
End With
When I look in cell A2, I see this for the expression: "=A65301=A65300". Any idea why it isn't starting with "=A2=A1" as I specified in the code?
View 7 Replies
View Related
