CODEWITHSUNDEEP
google-sheetsconditional-formatting
AVAVT
AVAVT

Reputation: 7143

Changing cell background and foreground color with different conditional formatting rules

I'm coloring the background of my sheet's rows with some arbitrary formula in conditional formatting for visual purpose — for example, striped with =ISEVEN(ROW()), or based on the text contents of a particular column, =$B1="Groceries".

But now I also want some cells in the list to have custom foreground, text color according to another, unrelated formula. For example, red if it starts with +, red/green if it's above/below zero, blue for a substring match, etc.

Problem is, each rule tries to format both background and foreground color of a cell, and one rule always precedes the other. The lower rule makes the background light green, then the higher rule makes the text red... and even though I didn't instruct the higher rule to affect background (it's the default white), it still overrides the lower rule's background. So now it's red text on white background — the lower rule is totally ignored.

I could manually create "permutations" of each of the possible combinations: for example, light green background and red text if =AND($B1="Groceries",C1>0), light green background and green text if =AND($B1="Groceries",C1<0), light blue background and red text if =AND($B1="Laundromat",C1>0) and on and on... but this gets unruly and very tedious, especially if I have a lot of possibilities for both foreground and background.

Is there any way to instruct a conditional formatting rule to format only background or foreground?

Upvotes: 5

Views: 2135

Answers (3)

Joseph Ishak
Joseph Ishak

Reputation: 1276

From my research, it does not appear possible to do what you are requesting through the Google Sheets UI. However, if you are willing to use the REST api and google sheets developer, you may be able to do this. There is more information here:

https://developers.google.com/sheets/api/samples/conditional-formatting

Basically you still create the conditional rules but it looks like you have more granularity to change independent items rather than all or nothing. For example, there is a recipe to only change the foreground and make a value italics without touching the background.
https://developers.google.com/sheets/api/samples/conditional-formatting#date-text

There is also another rule to only change the background to apply a color gradient without modifying the text color. https://developers.google.com/sheets/api/samples/conditional-formatting#color-gradient

Your question doesn't specify programming language or client (other than using the UI settings). Are you using the UI because you didn't know about the API options or is the UI required (ie not willing to try the REST API)?

Upvotes: 1

redpandasuit
redpandasuit

Reputation: 63

I would make an attempt at using a custom formula (within the conditional formatting drop box options) that changes the font colour based on the "+" being present in the cell.

I would use a custom formula such as:

=RegExMatch(A1, "\+")

And then format the conditional formatting to suit your text colour choice.

This should search the cells in the defined column for the "+" and then reassign your desired font colour if the "+" expression is within the cell.

NOTE: The A1 (range) dictates the column that is being scanned and will likely have to be adjusted to suit your need.

ALSO NOTE: The "\" before the "+" symbol is there to signify that the plus symbol is a "special character".

Examples of Regular Expressions

Upvotes: -1

emjaySX
emjaySX

Reputation: 148

When you create conditional formatting rules, you can include a fill colour (or not), font colour/style (or not), and much else, for any cells where the condition(s) is met.

So, for your =ISEVEN(ROW()) simply don't include any font colour option in its rule, similarly in your contains + rule, simply don't include any fill settings in its rule

Upvotes: -1

Related Questions