Tag Info

New answers tagged

0

If you go back and edit your conditional formatting formula, just change $B$1 to $B1. When creating a conditional format, Excel uses absolute references by default. Removing the $ from in front of the one makes the row reference relative.


0

Your issue could be where you start to apply the conditional formatting. The answer with = AND ($B2 > 0, $F2 = "") assumes you are applying your conditional formatting row 2 of any column. For example if you are applying your conditional formatting rule to an entire column the first row that the rule is applied needs to be the row number in your formula. ...


0

I do not know if the solution you are looking for should involve macro (VBA) or not. Anyway, as a first pass I'd suggest the following steps to achieve what you want. Look at the following figure Then use conditional formatting to change the color of the cells as you want. Note: If you do not like to see the words "YES" "NO" then you can select the cells ...


2

Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be able to extrapolate the setup for False = No. It's important to note that it will only change the apparent value in the cell and not the actual value. If you ...


0

Example: Data Result Result 1 FALSE NO 12 TRUE YES 12 FALSE NO 33 Formula in C3: =IF(AND(A4=A5),"YES","NO") Formula in B3: =AND(A3=A4)


3

Use the IF function: =IF(A1=B1, "Yes", "No")


0

You can use the ISNUMBER and SEARCH functions together to find text within a string. Change your formula to the following to search for "SBC" within the text contained in cell C2. =AND($D2>=1000,ISNUMBER(SEARCH("SBC",$C2))) If the search finds it and D2 is greater than $1,000.00, the AND statement will be true and the conditional format will be ...


0

The conditional formatting formula I would apply to column C is: =AND(INDIRECT("RC",FALSE)=0,INDIRECT("RC[-1]",FALSE)>1) The FALSE arguments to INDIRECT indicate it should use RC-style references instead of A1-style references. An RC-style reference of RC refers to the same cell, while the RC[-1] notation indicates the cell of the same row and one ...


0

Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C). Are you sure you need to keep columns A and B ...


0

Create a second identical conditional formatting rule set for the same cell range as the original. Edit the new rule and modify it so that Select a Rule Type = Format only cells that contain; and, Edit the Rule Description: = Blanks; and, Preview: = No Format Set. Click Ok, to set the rule. In the Conditional Formatting Rules Manager window, drag the rule ...


1

Check if cell is blank AND if today is 14 days away from the date in b1. Select the B column Select conditional formatting > New Rule > Use a Formula... =AND((B1-TODAY()<14),(NOT(ISBLANK(B1)))) Select Fill Style for red


0

You don't need VBA at all. You just need a better understanding of the flexibility behind conditional formats. Because of that, I'm going to answer the question even though it's a poor one. You can sort by column D as is and the totals row will be at the bottom if it's the only one with a blank in it. Apply your conditional formats to the entire column as ...


1

This is similar to Gary’s Student’s approach.  Define the following VBA function: Function MyRowHidden(ref As Range) MyRowHidden = Rows(ref.Row).Hidden End Function See How do I add VBA in MS Office? if you need help with that.  Now you can use MyRowHidden(cell) to check whether the row containing cell is hidden. The way I devised to solve the ...


0

Edit: This is not an ideal answer. It requires that the rows are sorted by category; if they are not, it will fail. It also requires adding an extra column (that you may hide) used to determine if the row is hidden or not. I will prefer and accept any answer that matches the title of the question, i.e. based on the value of the preceding visible row, not ...


0

Instead of subtotal using a sum on another column, you can use subtotal using counta to see if a (known-non-blank) cell is hidden or not. For example, if column A will normally be visible (unless the row is hidden) = IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" ) You can put this formula in a column that may be hidden, and it will still work. ...


0

To detect if the row above the active cell is Hidden, run this macro: Sub WhatsAboveMe() Dim r As Range Set r = Selection With r If .Row = 1 Then Exit Sub End If If .Offset(-1, 0).EntireRow.Hidden = True Then MsgBox "the row above is hidden" Else MsgBox "the row above is visible" End If End With End Sub


0

I found a new way to do this! You lock the cells that are conditionally formatted. When you cut and paste, the conditional formatting stays the same!


0

You only need the condition to reference cells for one row, I.e If "applies to" range is C2:F172 Then make the formula apply to the first cell, like this =AND($B2="y",ISBLANK(C2)) The formula implicitly adjusts for each cell in the range That will format all blank cells in rows where B column is "y".....or do you want all cells in a row formatted if ...



Top 50 recent answers are included