In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
I would like to use conditional formatting to highlight the max value if the constraints are not violated.
For example, Row 91 - Profit Row 94 - Constraint A (750<=A<=855) Row 95 - Constraint B (12<=B<=17) So, I tried to type this in conditional formatting but it didn't work >< =MAX(IF(AND(B94>=750,B94<=855,B95>=12,B95<=17),$B$91:$Q$91,0))
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).
I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.
So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.
The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..
Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB: Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
I have a database function in which I cannot use an "OR" operator. I can add "+" on to another (Ex. Sum(a1:b1)+sum(b2:c3)). The function itself is 80-90 characters long. So after I add a few together the cell runs out of capacity. I have a analysis that simply does not lend itself to listing out individual functions and then summing them.
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.
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.
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.
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...
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.
I have on occaision seen -- within a formula. e.g =SUMPRODUCT(--(PMonth=$A3),--(PName=$A$1),PLTime). Could someone explain the relavance/effect or use of this as it seems outside of standard operators.
Excel has operators for Less than, Greater than, Equal to, etc., etc. Does anyone know if there is a Between operator? (Maybe an undocumented or little-known feature like DATEDIF)?
There is a Between Feature in the Conditional Formatting Dialog Box - it would sure be nice to be able to use it in a non-formatting formula, or maybe you could explain how you pros achieve a "between" result without resorting to VBA.
revised this code to reflect the "not equal to". I want the user to only enter "In" or "Out." Therefore, If cells b19:B26 does not have either word, then the message box will prompt the user to enter the correct word again. I think my code needs to be revised into some kind of loop.
While proof-reading my code, I've noticed that these two true select cases ("S" and "W") shown below wouldn't "yield" what I want. I've tried to replace AND (highlighted in red) with "," (comma) but I still get the same result - it still returns a value evaluating either one of two conditions but not BOTH:
Code: sChar = Split(Waypoint, ",") For j = LBound(sChar) To UBound(sChar) Step 3 Lat = sChar(j) Lon = sChar(j + 1) Elev = Val(sChar(j + 2)) Next j
Iīm trying to find a way to get a formula which can look if there is a character (letīs say it is "-") in a formula which is, by the way, variable (letīs say each cell has different values like: =899+59-6 and so it goes and goes), and brings me a true or false value.
Specifically it is like this:
I have formulas to be evaluated like the following:
=899+59-6
I need a formula which can look into the formula and if it finds the character "-" or a "+", brings me a true or false message.
Can you use a variable as an operator? For example:
Dim op op = ">" If Var1 op Var2 Then MsgBox "It Works!"
I have parameters stored in a database and each parameter includes an operator. When I compare a given value to the parameter, I was hoping to do that dynamically by extracting the operator from the parameter and using that in the comparison. Otherwise, the only way I can see to do it is create a Function with a Select Case list for all the operators to check the parameter, do the comparison, then return the value to the Function.
filter a pivot table that contains these 3 column fields: User 1, User 2, and User 3.
However, when I want to see the results for "John" in all those fields, I need to use one filter at a time. Is there any other way to see all results of the Pivot Table where "John" is a User, no matter if he is the 1, 2 or 3 user?
When I run this code it does the exact opposite of what I want it do (if not contained in that character list then delete). So basically if it not in the character list then delete, but it if is in the character list it deletes....
