Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-18-2023, 01:32 AM
Wordfinder Wordfinder is offline How to color the specific cells at once? Windows 10 How to color the specific cells at once? Office 2019
Novice
How to color the specific cells at once?
 
Join Date: Jul 2019
Posts: 6
Wordfinder is on a distinguished road
Default How to color the specific cells at once?


Hi,

I have a long table with thousands of cells, some cells should apply background colors, in my case; the cells have a word inside themselves (like Name) and I'd like to give a color to them immediately, rather than select one by one and apply color.
Reply With Quote
  #2  
Old 09-18-2023, 08:03 AM
Charles Kenyon Charles Kenyon is offline How to color the specific cells at once? Windows 11 How to color the specific cells at once? Office 2021
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,140
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Simplest method - won't give cell color, but rather word color.
Use Replace to find the target word and replace with the same word applying color and/or text shading.
You can get to the formatting options by clicking More on the Replace dialog.
Anything else will require a macro.


If you want a volunteer here to write a macro for you, I would suggest that you attach a sample file so they will not have to try to recreate your situation to give you a fix.
Reply With Quote
  #3  
Old 09-18-2023, 12:09 PM
kilroyscarnival kilroyscarnival is offline How to color the specific cells at once? Windows 10 How to color the specific cells at once? Office 2021
Expert
 
Join Date: May 2019
Posts: 345
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Something I considered - could you do the entire table in Excel, where you can use conditional formatting? There's a pre-set condition that says "cell contains text" that would work even if your condition were "apple" and you had "applesauce" and "crabapple" options, or the like. The tricky part is getting a loooong Excel table into a Word document, if indeed it needs to go back to being a Word doc. You could insert an Excel sheet into Word, but getting it to paginate across multiple pages is not something I've been able to do neatly. But if you create it in Excel and "plain" paste it into Word, it becomes a table and in my quick experimenting, it does retain the cell background colors. I just had to make it fit and repeat the header row, etc.
Reply With Quote
  #4  
Old 09-18-2023, 01:43 PM
East East is offline How to color the specific cells at once? Windows 10 How to color the specific cells at once? Office 2019
Novice
 
Join Date: Aug 2023
Posts: 7
East is on a distinguished road
Default

Try this macro (created by AI):

Code:
Sub ShadeCellsInTable()
    Dim selectedRange As Range
    Dim tbl As Table
    Dim cell As Cell
    Dim findText As String
    Dim alertMessage As String
    
    ' Set the text you want to find
    findText = "Name"
    
    ' Set the alert message
    alertMessage = "Please select a table."
    
    ' Check if a range is selected
    If Selection.Type <> wdSelectionIP Then
        Set selectedRange = Selection.Range
        ' Check if the selection is a table
        If selectedRange.Tables.Count > 0 Then
            Set tbl = selectedRange.Tables(1)
            ' Loop through each cell in the table
            For Each cell In tbl.Range.Cells
                ' Check if the cell contains the find text
                If InStr(cell.Range.Text, findText) > 0 Then
                    ' Shade the cell if it contains the find text
                    cell.Shading.BackgroundPatternColor = wdColorGray25
                End If
            Next cell
        Else
            ' Show an alert if a table is not selected
            MsgBox alertMessage, vbInformation, "Word Macro"
        End If
    Else
        ' Show an alert if a range is not selected
        MsgBox alertMessage, vbInformation, "Word Macro"
    End If
    
End Sub
Reply With Quote
Reply

Tags
cell color, color, table cell



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to color the specific cells at once? Remove highlight with specific color rekent Word VBA 2 08-01-2020 10:59 AM
How can I transfer specific data from email to specific excel cells? Mauro Outlook 1 11-11-2019 09:17 PM
Color in cells Stefaan1973 Excel 2 08-13-2018 06:26 AM
Linking Specific text fields in PP to specific cells in an Excel table GWRW1964 PowerPoint 0 02-26-2018 07:37 AM
How to color the specific cells at once? Need cells to autofill with color Silver Rain 007 Excel 1 10-22-2009 08:19 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft