Insert text into the background of a cell

Question

I am looking for a way to insert text into the background of a cell, so that I can still enter numbers on top of that text - similar to a watermark except for an individual cell. Any ways to do this, preferably without using a macro (but open to these solutions as well)?

Answer 1

Similar to Andrews post, this is the VBA version which formats the shape correctly and also allows direct selecting of cells.

Code MODULE:

Sub watermarkShape()
Const watermark As String = "watermark"
Dim cll As Range
Dim rng As Range
Dim ws As Worksheet
Dim shp As Shape

    Set ws = Sheet1
    Set rng = ws.Range("A1:F10") 'Set range to fill with watermark

    Application.ScreenUpdating = False

    For Each shp In ws.Shapes
        shp.Delete
    Next shp

    For Each cll In rng

        Set shp = ws.Shapes.AddShape(msoShapeRectangle, 5, 5, 5, 5)

        With shp
            .Left = cll.Left
            .Top = cll.Top
            .Height = cll.Height
            .Width = cll.Width

            .Name = cll.address
            .TextFrame2.TextRange.Characters.Text = watermark
            .TextFrame2.TextRange.Font.Name = "Tahoma"
            .TextFrame2.TextRange.Font.Size = 8
            .TextFrame2.VerticalAnchor = msoAnchorMiddle
            .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextFrame2.WordWrap = msoFalse
            .TextFrame.Characters.Font.ColorIndex = 15
            .TextFrame2.TextRange.Font.Fill.Transparency = 0.35

            .Line.Visible = msoFalse
'            Debug.Print "'SelectCell (""" & ws.Name & """,""" & cll.address & """)'"
            .OnAction = "'SelectCell """ & ws.Name & """,""" & cll.address & """'"

            With .Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                .Transparency = 1
                .Solid
            End With

        End With


    Next cll

    Application.ScreenUpdating = True
End Sub

Sub SelectCell(ws, address)
    Worksheets(ws).Range(address).Select
End Sub

UPDATE:

the example below assigns a watermark of the cell address to odd rows and leaves the even rows as the constant watermark. This is an exaple based on my comment that any cell can be assigned any watermark text based on whatever conditons you want.

Option Explicit

Sub watermarkShape()
Const watermark As String = "watermark"
Dim cll As Range
Dim rng As Range
Dim ws As Worksheet
Dim shp As Shape

    Set ws = Sheet1
    Set rng = ws.Range("A1:F10") 'Set range to fill with watermark

    Application.ScreenUpdating = False

    For Each shp In ws.Shapes
        shp.Delete
    Next shp

    For Each cll In rng

        Set shp = ws.Shapes.AddShape(msoShapeRectangle, 5, 5, 5, 5)

        With shp
            .Left = cll.Left
            .Top = cll.Top
            .Height = cll.Height
            .Width = cll.Width

            .Name = cll.address
            If cll.Row Mod 2 = 1 Then
                .TextFrame2.TextRange.Characters.Text = cll.address
            Else
                .TextFrame2.TextRange.Characters.Text = watermark
            End If
            .TextFrame2.TextRange.Font.Name = "Tahoma"
            .TextFrame2.TextRange.Font.Size = 8
            .TextFrame2.VerticalAnchor = msoAnchorMiddle
            .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextFrame2.WordWrap = msoFalse
            .TextFrame.Characters.Font.ColorIndex = 15
            .TextFrame2.TextRange.Font.Fill.Transparency = 0.35

            .Line.Visible = msoFalse
'            Debug.Print "'SelectCell (""" & ws.Name & """,""" & cll.address & """)'"
            .OnAction = "'SelectCell """ & ws.Name & """,""" & cll.address & """'"

            With .Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                .Transparency = 1
                .Solid
            End With

        End With


    Next cll

    Application.ScreenUpdating = True
End Sub

Sub SelectCell(ws, address)
    Worksheets(ws).Range(address).Select
End Sub

Answer 2

You can use a custom number format (select the cell(s), hit Ctrl+1, number formats, custom) to specify a light-grey text to display when the cell value is 0 - Color15 makes a nice watermark color:

[Black]000000;;[Color15]"(order number)";@

No messy shapes, no VBA, and the watermark disappears when the value is actually filled up.

And if you absolutely need to do it in VBA, then you can easily write a function that builds the format string based on some parameters:

Public Function BuildWatermarkFormat(ByVal watermarkText As String, Optional ByVal positiveFormat As String = "General", Optional ByVal negativeFormat As String = "General", Optional ByVal textFormat As String = "General") As String
    BuildWatermarkFormat = positiveFormat & ";" & negativeFormat & ";[Color15]" & Chr(34) & watermarkText & Chr(34) & ";" & textFormat
End Function

And then you can do:

myCell.NumberFormat = BuildWatermarkFormat("Please enter a value")
myCell.Value = 0

And you can still supply custom formats for positive/negative values as per your needs; the only thing is that 0 is reserved for "no value" and triggers the watermark.

myCell.NumberFormat = BuildWatermarkFormat("Please enter a value", "[Blue]#,##0.00_)", "[Red](#,##0.00)")
myCell.Value = -25

Answer 3

• Select the Cell where you want to make the Background.
• Click "Insert" and insert a rectangular Shape in that location.
• Right click on the shape - select "Format Shape"
• Goto "Fill" and select "Picture or texture fill"
• Goto “Insert from File” option
• Select the picture you want to make water-mark
• Picture will appear at the place of rectangular shape
• Now click on the picture “right click” and select Format Picture
• Goto “Fill” and increase the transparency as required to look it like a “Water Mark” or light beckground
• This will get printed also.

taken from here

Answer 4

Type your text in a cell anywhere. Copy it and it will be saved on the clipboard. Insert a rectangular shape anywhere. Right click and choose "Send to back". This will make sure it will be at the background. Right click and "Format Shape". Do to tab "Fill" and click on "picture or texture fill". At the "insert from" choose "clipboard". Now whatever text you have copied onto your clipboard will be in the rectangular shape. Resize the shape to fit the cell(s) you desired. Adjust however you like for example remove the rectangular lines, add shadow, change font, remove background etc.