I'd like to color code a sheet based on two criteria: If the cell in the range contains specific text "A1" and if another cell, $X$7, contains an X or is not blank. I can't seem to get AND or IF or IFS to work in Sheet's conditional formatting.
3
-
2Welcome. Please add more details like examples of the formulas that you tried and the steps that you followed to apply those formulas. Also include a brief description of your search/research efforts as is suggested in How to Ask.– Rubén ♦May 14, 2020 at 0:48
-
2=IF($X$&="x", true) causes the range to turn color. I tried combinations of =AND($X$&="x", "text") and =IFS($X$&="x", true, "text", true). Nothing turns colors. I'm not sure I'm using those formulas correctly or if I need to try a negative =IFS($X$&="", true, "text", true).– AcireMay 14, 2020 at 1:37
-
Please read how to and share a test sheet so as you can be easier helped.– marikamitsosMay 15, 2020 at 15:45
Add a comment
|
1 Answer
Try this as a custom formula:
=if(isblank($X$7)=false,if(countif(F1:H10,"A1"),"colour me",""))="colour me"
- You said
$X$7... contains an X or is not blank. In that case, you don't need to test for "X", but just if the cell is not blank. You are looking for a value of "false". - the test for "A1" is a simple
countif(); there may be more sophisticated methods available, but this seems to okay here.
- Open Conditional formatting,
- Select "Format cells if"
- From the dropdown, select "Custom formula is"
- Paste the formula in the cell marked "Value of formula"
- Click Done.
-
Thank you! That mostly works, as it colors based on the two conditions, but it colors the column containing the text down to and inlcuding the cell with the text, and then half of the next column to an unclear point.– AcireMay 14, 2020 at 11:29
-
@Acire My apologies. What I should have made clear is that you also need to define the range that the conditional formatting applies to. but it colors the column containing the text down to and including the cell with the text, and then half of the next column to an unclear point. that's not helpful. Did you adjust the formula to reflect the range that you want to format? Please describe the range to be conditionally formatted, and clarify whether this range includes the text "A1".– TedinozMay 14, 2020 at 11:37
-
I want to to search B1:T200 for "A1" and color only the cells that contain "A1". Right now, it's coloring starting at B1 down and including the correct cell, for example, G9, and also colors to H8. So wherever the cell exists that contains "A1", it colors from B1 down to that point and includes and additional column. Does that clarify?– AcireMay 14, 2020 at 11:46
-
"and color only the cells that contain "A1"" - I don't think that you mentioned that before. Okay something to thing about.– TedinozMay 15, 2020 at 0:44
-
@Acire Try this:
=IF(B1="A1", true)*if(isblank($X$7)=false,true). BTW, I accidentally entered "a1" in a cell and noticed that the cell was highlighted by conditional formatting. Not sure if this is a deal-breaker.– TedinozMay 15, 2020 at 2:25