Need formula

[CRAIG20]

Hi I need a conditional format formula.

It simply is this.

If a cell is blank I want it to enter an asterisk '*' in the cell.

Thanks

Craig

 

[jasonb75]

You can't do that with a conditional format.

Is the blank cell the result of a formula?

 

[VoG]

That isn't possible with a formula or conditional formatting. Perhaps a macro: select the range and run

Sub BlAsk()
Selection.SpecialCells(xlCellTypeBlanks).Value = "*"
End Sub

 

[CRAIG20]

No. Its just a table full of figures but some cells are blank. However I need to import the table into another program, but it won't accept blank cells. I just need to enter anything at all really into the blank cells, so they are not blank anymore.

 

[CRAIG20]

The macro worked.

many thanks for that.

 

[Misca]

I don't think the conditional formatting can do that. It can change the interior color or fill of the cell and the font etc. but it can't change the value of the cell. And it looks like that's what you're looking for.

You can use a normal IF-formula to return the asterix to the formula cell if the reference cell is empty (ie. something like =IF(A1="","*",A1)). Or you can use a simple macro to fill the empty cells in the given range with asterixes if you want to:

Paste the following in the worksheet module in your VB Editor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim c As Range

    For Each c In Range("A1:H10")   'The range you want to fill with asterixes
        With c
            If IsEmpty(.Value) Then .Value = "*"
        End With
    Next c

End Sub