Contitional watermark?

[Jock]

If, for instance, "paid" is selected in cell K5, is it possible to
automatically have 'paid' appear in large font accross the range (D5:G8) like
a watermark?

 

[jlclyde]

If, for instance, "paid" is selected in cell K5, is it possible to
automatically have 'paid' appear in large font accross the range (D5:G8) like
a watermark?

Yes, it is possible. I am assumign that you have a pull down for the
paid column. Merge D5:G8. Enter this formula into D5
=IF(K5="Paid","Paid","")

You can also nest more ifs together to make the watermark multiple
things. Like =IF(K5="Paid","Paid",IF(K5="Donkey","Donkey",""))
Jay

 

[Jock]

Thanks Jay,
However, I have data in the range and therefore can't merge the cells.

 

[Gord Dibben]

Not without VBA to lay a shape over the cells.

Here is one method.......................

Create a rectangle using the drawing toolbar, add the text "Paid" to the shape.

Size the shape to fit over D5:G8.......see below for a macro to change the name
of the shape to "paid"(no quotes)

Give the shape a transparent background and the text font to size 36 colored a
light pattern.

Add this event code to the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim opic As Shape
ActiveSheet.Shapes(msoShapeRectangle).Visible = False
With Range("K5")
For Each opic In ActiveSheet.Shapes
If opic.Name = .Text Then
With ActiveSheet.Range("D5")
opic.Visible = True
opic.Top = .Top
opic.Left = .Left
Exit For
End With
End If
Next opic
End With
End Sub

Thanks to John McGimpsey for the original code.

To change the name to "paid" enter the word paid in A2 then run this macro.

Sub Rename_Pics22()
Dim Pic As Shape
Dim rng As Range
Dim i As Integer
On Error GoTo endit

Set rng = ActiveSheet.Range("A2")
For Each Pic In Selection.ShapeRange
Pic.Name = rng.Offset(i, 0).Value
i = i + 1
Next Pic
Exit Sub

endit:
MsgBox "there is a picture by that name, re-type a name"
End Sub


Gord Dibben MS Excel MVP

 

[Jock]

Interesting stuff Gordon, but can I ask a bit more??
In colomn K, every 4th or 5th line will have a drop down list in which one
of the options is "Paid".
When the option "Paid" is selected, I'd like the rectangle to appear (with
paid in it) from the cell immediately to the left back to A on the same row
and down four rows.
Is that sort of scenario do-able?

Cheers

 

[Gord Dibben]

I would say it can be done.

I will work on it and come up with a workbook I can send to you.

Email me if you want this to happen.

Change the AT and DOT to get my address.


Gord