FAQ Database Discussion Community


Conditional Formatting using Date Comparison

excel,vba,date,excel-vba,conditional-formatting
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 & "="...

Switch expression for conditional formatting in SSRS 2008 R2

sql-server,reporting-services,switch-statement,ssrs-2008-r2,conditional-formatting
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...

Conditional formatting VBA scripting (not IN excel)

vba,scripting,conditional-formatting
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,...

Excel - How to conditionally lock and unlock a particular cell using VBA.

excel,vba,excel-vba,locking,conditional-formatting
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,...

Excel 2010: Conditional Format Class Schedule based on Subject name

excel,excel-2010,conditional-formatting
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...

Conditional formatting based on value in corresponding row of named range

excel,conditional-formatting
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...

Excel 2010 MATCH function returns an error in conditional formatting

excel,excel-formula,conditional-formatting
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))...

If the row contains specific text highlight row by row where two columns are the same

excel,excel-formula,worksheet-function,conditional-formatting,countif
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...

Discrepancy between results of EXACT function and Conditional Formatting

excel,excel-formula,worksheet-function,conditional-formatting,exact-match
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...

Conditionally Format Minimum, Unique Value

excel,excel-formula,conditional-formatting
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....

Excel: conditional formatting, check if column contains text from other sheet

excel,condition,conditional-formatting
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...

Excel Conditional Formatting Data Bars based on Color

excel,excel-vba,excel-2010,conditional-formatting
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...

Wrong cell when conditional formatting with values

excel,excel-2010,conditional-formatting
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....

Parametrized Conditional formatting in Calc

function,conditional-formatting,libreoffice-calc
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...

Conditional Formatting ClosedXML Error

c#,conditional-formatting,closedxml
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,...

Excel 2013 VBA Macro Needed for Highlighting with Multiple Conditionals

excel,excel-vba,conditional-formatting
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...

How do I conditionally format cells based on an indirect formula that references “moving” cells

arrays,excel,vba,excel-vba,conditional-formatting
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?...

Conditional Formatting for every row

excel,excel-2010,conditional-formatting
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...

Conditional Formating Comparing date between 2 columns

excel,excel-formula,conditional-formatting
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....

Range Loop for Data Clean-up

excel,excel-vba,for-loop,boolean,conditional-formatting
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"...

Conditional formatting not seeing updates to dynamic range

excel,range,conditional-formatting
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...

Conditional Formatting to show progress against a target

excel,target,conditional-formatting
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...

Highlight cells based on 10-15 values and post alternatives

excel,excel-formula,highlighting,conditional-formatting
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...

SSRS conditional formatting (Using AND and OR)

algorithm,reporting-services,ssrs-2008,conditional-formatting,iif
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...

If particular cell is NOT BLANK copy down FORMULA from above cell

conditional-formatting
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...

I want to create a setup that formats rows based on a specific value

excel,excel-2007,conditional-formatting
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:...

Conditional formatting if a cell does not equal one of three values [closed]

excel,excel-formula,conditional-formatting
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...

Excel add traffic light

c#,excel,excel-2013,conditional-formatting
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 }...

Find all listed items in another list

excel,excel-formula,conditional-formatting
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...

Multiple criteria conditional formatting in a PivotTable

excel,table,pivot-table,excel-2013,conditional-formatting
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...

Identify all instances of a set where any one instance of the set is paired with a specific value

excel,excel-formula,worksheet-function,conditional-formatting,countif
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...

Google Sheets - Conditional Formatting based on validation errors

google-spreadsheet,conditional-formatting,data-validation
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...

Show number sign at the right side of number using cell formatting

excel,formatting,excel-formula,worksheet-function,conditional-formatting
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...

Conditional Formatting for Filtered area

excel-2013,conditional-formatting
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...