基于单元格填充色添加白色边框的条件格式设置需求

Hey there! Let's figure out how to get those white borders automatically applied to your colored cells—even when you sort the data. Here are two reliable solutions that work just like conditional formatting:

This uses Excel's built-in conditional formatting with a custom formula to target cells by their fill color. It’ll update automatically and stick with cells when you sort.

1. First, create a custom named range to pull cell fill color data:

   • Go to the Formulas tab → click Define Name
   • Name it something like CellFillColor
   • In the "Refers to" box, paste this formula:

     =GET.CELL(38,INDIRECT("RC",FALSE))
     

   • Click OK

2. Set up the conditional formatting rule:

   • Select your target data range
   • Go to the Home tab → Conditional Formatting → New Rule
   • Choose "Use a formula to determine which cells to format"
   • In the formula box, enter:

     =CellFillColor=XX
     

     Replace XX with the index number of your target fill color. To get this number: select a cell with the fill color you want, then type =CellFillColor in any empty cell and press Enter—this will give you the index.
   • Click Format → go to the Border tab
   • Select a white line style, then choose the borders you want (e.g., all borders)
   • Click OK twice to apply the rule

Note: If the borders don’t update right after changing a cell’s fill color, press F9 to refresh the calculation.

If you want zero manual refreshes and full automation, use this VBA approach. It’ll trigger automatically when you change cell colors, sort data, or edit the sheet.

1. Open the VBA editor:

   • Right-click your worksheet tab (e.g., "Sheet1") → select View Code

2. Paste this code into the editor window:

   Private Sub Worksheet_Calculate()
       ApplyWhiteBordersToColoredCells
   End Sub
   
   Private Sub Worksheet_Change(ByVal Target As Range)
       ApplyWhiteBordersToColoredCells
   End Sub
   
   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       ApplyWhiteBordersToColoredCells
   End Sub
   
   Sub ApplyWhiteBordersToColoredCells()
       Dim rng As Range
       Dim cell As Range
       ' Replace with your actual data range (e.g., A1:D100)
       Set rng = Me.Range("A1:D100")
       ' Replace with your target fill color's RGB values (e.g., RGB(255,255,0) = yellow)
       Dim targetColor As Long
       targetColor = RGB(255, 255, 0)
       
       ' Clear existing white borders first (optional)
       rng.Borders.LineStyle = xlNone
       
       ' Loop through cells and apply white borders to matching colors
       For Each cell In rng
           If cell.Interior.Color = targetColor Then
               With cell.Borders
                   .LineStyle = xlContinuous
                   .Color = vbWhite
                   .Weight = xlThin
               End With
           End If
       Next cell
   End Sub
   

3. Customize the code for your needs:

   • Update Set rng = Me.Range("A1:D100") to match your data range
   • Change targetColor = RGB(255, 255, 0) to the RGB values of your target fill color (you can find these in Excel's color picker under "More Colors" → "Custom")

4. Save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep the macro active.

Now whenever you sort, change a cell’s fill color, or edit the sheet, the white borders will automatically adjust to stay with the colored cells.

内容的提问来源于stack exchange，提问作者Nick Alvey