Google Sheets: Conditional Formatting Query

[danhenshy23]

Hi,

I have a series of data running across a number of columns. Where a certain word appears in the data, I am wanting to colour fill the 3 cells to the left of it.

Using the below example, where the word Dog appears, I would like the 3 cells to the left of the cell containing the word Dog to be filled in.

Is this possible?


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Dog[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Pig[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Cat[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Dog[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]Owl[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Dan

 

[Eric W]

Re: Conditional Formatting Query

Sure. Select the columns that you want to be highlighted, in this example A:C and E:G. Or A:G if you want. Click Conditional Formatting > New Rule > Use a formula, and enter:

=OR(B1:D1="dog")

click Format... and choose a fill color. If you enter multiple rules, make sure that the cell reference in the formula refers to the top left cells in the range you select. Also, if some of your text might be the word "dog", you can select your range, let's say E:G, then use this formula:

=$H1="dog"

This uses absolute referencing for the column with "dog".

Hope this helps!

 

[Johnny Thunder]

Re: Conditional Formatting Query

I went the VBA Route. Hope this helps. this will highlight any range when the word "Dog" is found. Modify if needed.

Sub HighlightString()


Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range


'What value do you want to find?
  fnd = "Dog"
      
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)


'Test to see if anything was found
  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If


Set rng = FoundCell


'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
    'Find next cell with fnd value
      Set FoundCell = myRange.FindNext(after:=FoundCell)
    
    'Add found cell to rng range variable
      Set rng = Union(rng, FoundCell)
    
    'Test to see if cycled through to first found cell
      If FoundCell.Address = FirstFound Then Exit Do
      
  Loop


'Highlight Found cells yellow
  rng.Interior.Color = RGB(255, 255, 0)


'Report Out Message
  MsgBox rng.Cells.Count & " cell(s) were found containing: " & fnd


Exit Sub


'Error Handler
NothingFound:
  MsgBox "No cells containing: " & fnd & " were found in this worksheet"


End Sub

 

[danhenshy23]

Re: Conditional Formatting Query

Thanks both.

I'm having to use Google Sheets for this file, so don't think VBA would be possible?

At for your suggestion Eric, my data runs right the way across to column EU with every 4th column being the one which might contain the word dog.
I've tried your formula but it looks like I would have to add a new rule for each section of data?

 

[Eric W]

Re: Conditional Formatting Query

You shouldn't have to add multiple rules. Select columns A:EU, then use the first formula I posted. Since it uses relative references, it'll adjust for the whole range. The one possible drawback is if you have "dog" in one of the other columns.

 

[Joe4]

Re: Conditional Formatting Query

I'm having to use Google Sheets for this file

That is a very important detail you want to be sure to mention in your initial question. Google Sheets and Excel are NOT the same thing...
I have added "Google Sheets..." to the thread title, and moved the thread to the "General Excel Discussion & Other Questions" forum.
This is where you will want to post Google Sheets questions.