I have a dynamic range called Categories which I created using this formula: =$D$2:OFFSET(INDEX($A$1:$A$30,COUNTA$A$1:$A$30)),0,3) In Conditional Formatting I have the =ISBLANK(D2) as the condition and Categories as range to which to apply this condition. Unfortunately, Excel converts my dynamic range to absolute values and only works once. If range boundaries...

See screenshot below. Column B is a named range curUnit. There is another named range unitCount with the value of 3. I am trying to conditionally format rows 8 through 12 using the formula =curUnit>unitCount, so that is the unit count is 3, rows 10-12 will be formatted. If I...

I'm trying to write a formula for a conditional formatting rule. The rule needs to trigger if any of the items in a list do not exist in another list. The following rule is similar but will only work for single values, for example if cell U18 contains A,B,C the...

I am trying to highlight the max value in each row of data to determine what year it falls in. Is there a simple way to apply it to the whole spreadsheet? The only way I can do it right now is by using the Format Painter on each individual...

I have a spreadsheet that has the below information; A1 - Target Revenue For Month B2 - Daily Revenue C2 - MTD Revenue What I want is conditional formatting (red/green) to show whether the MTD Revenue is on target to achieve the Target Revenue For Month. For example, the Target...

I need help writing an Excel macro that does quite a few things. My contribution was the macro to get the data into this format, but I'm stuck on highlighting, and don't even have the slightest idea how to proceed. Sorry this is super long, and thanks a bunch in...

When I enter the following formula in a cell it returns TRUE, but when I enter this into a new conditional formatting rule, it tells me I have an error. This is the formula: =ISERROR(MATCH(Table2[Website];Table13;0))...

I'm trying to conditionally format a column so the minimum, unique value is highlighted. So, for instance if the column looked like: 1 1 2 3 2 would be the minimum of the unique values. So, I'm trying to conditionally format the cell containing the number 2 in this example....

I have a dataset as below: I want to apply conditional formatting that will format a cell if the value in that cell is not 100, 50 or 0 (ie I want it to pick up A5 and format it) I'm new to conditional formatting and haven't been able to...

Title says it all. Any insight here? This code is most definitely not working: Sub MrFreeze(ByVal Target As Range) Dim cCell As Range Dim wksInput As Worksheet Set wksInput = Worksheets("Input") Set cCell = wksInput.Range("D14") If cCell.Value = "Yes" Then ActiveSheet.Unprotect Password = "password" Else ActiveSheet.Protect Password = "password", DrawingObjects:=True,...

I need to create formatting for numbers while keeping their actual value: If a negative is number entered, the minus sign needs to appear on the right-hand side of the number. If a positive number is entered, the plus sign needs to be on the right-hand side of the number...

I am trying to dynamically set the Style of a row in Calc. After giving up using a macro (see Changing cell background color in LibreOffice), I tried with Conditional Formatting rules (Format --> Conditional Formatting). I would like to set a parametrized Range, so that the conditional formula is...

I have a question regarding a complicated conditional formatting that I currently trying to put in place. The code is: =iif(Fields!ACT.Value = "N/V", "No Color", iif(Fields!ACT.Value = "N/K", "No Color", iif(Fields!ACT.Value = "N/A", "No Color", iif(Fields!NUM.Value < Fields!SHORT.Value , "Yellow", iif(Fields!NUM.Value > Fields!INC.Value, "Orange", iif((isnothing(Fields!ACT.Value <> "N/A" and Fields!NUM.Value) or...

I regularly encounter an issue on Excel 2010 when I create a new rule using a formula. The issue is reproducible on several of my computers. I select several cells (for instance A4:B143) and then create a new rule using one of the following formulas: =$A4="issue" =($A4="issue") then press Enter....

I need to compare 2 columns say A and B. Both A and B contains dates. If the date in B is less than date in A then I need to colour column B in red colour. I have used this formula ="IF($C$2<$B$2-1)" But it is not formatting the cells....

I've been developing a script that runs through a Txtfile, retrieves certain references, stores these refs in a variable in the form of ('ref1','ref2','ref3',...) so that after retrieving these refs I can query our database to retrieve 10 columns from the database. I add this result to a excel file,...

I'm observing an apparent discrepancy between the results of the EXACT function and the conditional formatting tool. Two cells in Column B have the same apparent text (about 30-40 words). But the "highlight cells" tool doesn't recognize them as duplicate values. Comparing the two cells with the EXACT function returns...

I cannot find a way to change the color of an Excel data bar based on value. Current formatting options only permit different colors based on positive/negative values. I'm currently using Excel 2010. I would like to have the color of a data bar show up as 'red' if the...

Can you apply conditional formatting comparing multiple criteria (e.g. two pivot table columns)? I have a PivotTable in Excel 2013 that compares variable percentage rates and their corresponding values. I want to apply conditional formatting to the PivotTable by highlighting each cell that is greater than the adjacent percentage rate...

I am trying to highlight data by row based on an a ref number. The system I pull the report from creates a new event and thus new row item for each action. So every time it breaches or is achieved a new line is created and I am only...

I am trying to create a conditional formatting rule in VBA that will change the cell background color only for the current date. I have a column of dates for the year. I am also using Office Excel 2010. oSheet.Range(Cells(iStartRow, 1), Cells(iLastRow, 1)).FormatConditions.Add xlCellValue, xlEqual, "=" & Cells.Address & "="...

I have a running sheet for my expenses. I would like to setup conditional formatting/Macro/ect. that looks in cells of THE CURRENT MONTH, then any cell that has the text "paid" FOR THE CURRENT MONTH it will apply a gray fill for THE ROW that the cell is in. EDIT:...

Stackoverflow community, I'm using Excel '10. I currently have an indirect formula that references 4 cells to determine the x,y coordinates for a sum range. Formula: =IFERROR(SUM(INDIRECT(G27&G26):INDIRECT(H27&H26)),"No Records") How do I use conditional formatting to highlight the cells that the formula is summing?...

I create a Excel file based on a DataSet, I want to add a column with a traffic light. The traffic light: int trafficCollor = 1; if (trafficCollor == 1) { DataRow NR = DT.NewRow(); NR[0] = ""; //add string with the code for the Excel traffic light yellow }...

First sheet: First name Last name Role Valeria Bianchi Admin Evelise Verdi Teacher Daniele Rossi Teacher Annamaria Neri Admim Anna Gialli Manager Anna Bella Proietti Teacher Simona Ciaociao Manager Elisabetta Lilla Admin Second sheet: Email Name [email protected] Valeria Bianchi [email protected] Daniele Rossi [email protected] Annamaria Neri [email protected] Anna Gialli [email protected] Simona...

I have a 1600-2000 rows of data in a spreadsheet that changes daily. I would like to highlight any row that contains such as 000000000000053851 in the R column. I had tried conditional formatting using this formula: =OR($R1="000000000000549543",$R1="000000000000267294,$R1="000000000000053851") but it seems to highlight a few rows that are blank in...

I'm exporting some data from access to excel using CloseXML, Im trying to do a Conditional Formatting with some dates, but I'm getting a NULL EXCEPTION REFERENCE ERROR. Here is what I have. using (OleDbConnection connection = new OleDbConnection(conecctionstring)) { string selectquery = "querystring"; using (OleDbDataAdapter selectCommand = new OleDbDataAdapter(selectquery,...

I currently use conditional formatting on columns M and N on a spreadsheet to fill duplicate number values using the formula: =COUNTIF($M1:$N1,M1)>1 I was asked by my boss if I could only conditionally format columns M and N if a specific text name in column G appears on that same...

This seems like it should be simple, but I have been getting error codes all afternoon: I have data that I am trying to clean up to graph (it's exported from a retaining system that sometime leaves "blank" cells not actually blank). So, I figured I could run a "IsNumeric"...

I have this conditional formatting expression (for background colour) which works fine: =SWITCH( Fields!Fire_Safety.Value = "F", "LimeGreen", Fields!Fire_Safety.Value = "i", "Red", Fields!Fire_Safety.Value = "E", "Yellow", TRUE,Nothing ) I have now changed the data table (via SQL) so that where the “F” was – there are now dates. I changed the...

I currently use the following formula in conditional formatting to highlight ever other row. =IF(ISBLANK(A1),"",MOD(ROW(),2)=0) However I would like to find a way to modify this to only apply to currently visible section of a filtered dataset. So for instance the above currently highlights every even row but if I...

Is it possible to set the conditional formatting of a cell based on whether or not the cell has a validation error? I have a drop down box and old data in the sheet that doesn't match up to the drop down box list needs to be added to my...

Excel 2010, creating a class schedule. Colour coded per class name. I have a table, broken in to 10 min amounts. 7 wide, 30 down. I want to conditionally format all cells that contain the letters ENG in green, all SCI in yellow & so on. Currently my table colours...

Basically I have the following formulas: Column J: =IF(ISNA(VLOOKUP(I2,CCG,1,FALSE)),"Out of Area",VLOOKUP(I2,CCG,1,FALSE)) Column K: =INDEX(ResponsibleAgency,MATCH(N3,LeftLookup,0),MATCH(J3,TopLookup,0)) Column L: =IF(ISNA(VLOOKUP(N3,PPLookup,2,FALSE)),"Missing F Code",VLOOKUP(N3,PPLookup,2,FALSE)) Column M: =IF(ISNA(VLOOKUP(N3,PPLookup,3,FALSE))," ",VLOOKUP(N3,PPLookup,3,FALSE)) Basically, I only want these formulas to activiate provided that column A is populated with a date. If there is no date in column A, I want...