Adding specific word in one cell, results in cells being filled with a certain colour

[MrAlexN]

Hope someone can help please. When I type a specific word into column B, I would like the result to show by automatically colouring certain cells in dark grey. For example, if I type BED in cell B16, I would like cells D16, E16, G16 i to fill in dark grey. If I cant have a grey cell, an X will be ok but prefer a colour. Many kind thanks

 

[MrAlexN]

Hi, I have 8 words I need to use. Just need it as simple as possible only because I am new to this

Kind regards

 

[Micron]

You're moving the goal posts and you're only showing 2 cases with different cell requirements. My code needs the first and last column numbers that are involved. Seems to me that AE is 31 and BH is 60 based on what you've shown now. If one of your other words needs to format out to column 75 then that is the max column number needed. I'd need to know what is the max column number or you can try this yourself:
Change For i = 3 To 9
to
For i = 31 To 60 (or use the leftmost column number involved in this, to the maximum column number involved in this).

ALSO - my code will copy whatever interior colors you have. F'rinstance, you get the grays you want in row 4, then later you format a cell in row 4 somewhere else (e.g. column D). Next time you add a word (say in row 21) and that word is found, you will get your grays where in the same columns as row 4, but you'll also get blue in D11 because you have blue in D4. I made the assumption that you'd want that to happen.

EDIT - forgot to mention that my code is based on the pattern you set up for the words. It won't make a cell gray right from the start. You'd have to set up the first 8 or whatever, examples.

 

[MrAlexN]

Hi, sorry Im confusing you all (and myself) I thought if I simplified what I needed and posted it, I could just take that and replicate what I needed. Apologies. I will take my actual document and remove a lot of it as its a big sheet and post it below again. Sorry for the trouble caused. Kind regards

 

[Micron]

Maybe try changing the numbers as I mentioned and see if that does the trick? Probably less work.

 

[MrAlexN]

Hi, this is the actual sheet. All I have done is deleted the data and loads of columns but the rows have not been changed.
What I need is when I type in one of 8 words like "BED" or "HIGH CHAIR" it auto fills specific cells on that line in dark grey
I have 80+ columns in different sections also with data so to do this manually every time is a real pain. The white cells will have dates added
I should have added this in the first place. I just thought I could apply the formula shown to me by just changing the column and row numbers/letters

 

[Micron]

Company coming today for Easter dinner and I'm the cook, so I am probably out of this for the day.

 

[MrAlexN]

No problem, thank you and have a lovely evening

 

[kevin9999]

See if the following code does what you want. You’ll need to add all the other words and columns yourself to the Case structure because you haven’t provided them. Just make sure Case Else is at the bottom.
You’ll also need to add all the columns references for all the words because you haven’t provided them. Just make sure each column is inside quotation marks and separated by a comma. Check the existing column references for “BED” and “CHAIR”, because you didn’t show CHAIR in your image (only HIGH CHAIR) and your column references in post #8 for BED indicate 4 columns shaded – whereas your image shows 6 columns shaded?!
I don’t know exactly which is the very first or the very last column involved, because you haven’t provided that. Assuming that column AE is the first column of interest, leave this: Target.Offset(, 28) at 28 (which means 28 columns to the right of column C). If not – change it. Assuming column BH is the last column of interest, leave this: .Resize(, 30) at 30 (which means 30 columns to the right of column AE). If not – change it.
If the actual interior colour (RGB(217, 217, 217)) isn’t what you want – change it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("C:C"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim c As Range, i As Long, j As Long, a
        Target.Offset(, 28).Resize(, 30).Interior.Color = xlNone
        i = Target.Row
        Select Case UCase(Target.Value2)
            Case Is = "BED"
                a = Array("AU", "AV", "BC", "BD")
            Case Is = "CHAIR"
                a = Array("AE", "AX", "BG", "BH")
            Case Else
                GoTo Continue
        End Select
        
        For j = LBound(a) To UBound(a)
            Range(a(j) & i).Interior.Color = RGB(217, 217, 217)
        Next j
    
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[MrAlexN]

Hi good morning. This all looks perfect. One question is if I add any columns that change the number between c and the first column (28) I will need to change the number. Problem is other people will have access to this document and may add some additional columns for data somewhere else.

Is there a way to fix it so the first column is the one with the green cell and an “i” in it as that will always be the first regardless of what changes before it

Kind regards and Happy Easter all

 

[kevin9999]

Is there a way to fix it so the first column is the one with the green cell and an “i” in it as that will always be the first regardless of what changes before it

You could, but you do realise that you would also have to change every column reference within your code ("AE" for example would have to become "AF" if only one column was added) - is that what you really want?