conditional formatting

[tnoe]

When using conditional formatting for a cell, How do you or can you do =
this?

Choose greater than or equal to zero. then divide by 2

example....

I type in a cell 1000 and how can I automatically get it to be divided =
in half?

Thank You
Tracey Noe

 

[Mudface]

You can't do that with conditional formatting, it only affects the appearance of a cell dependent on your specified condition, not its value. You can do this with a Change event in VBA- if you're interested repost with exact details of what you want to happen and which cell or range of cells will be used.

 

[Joe4]

I have been working on coding to do that. If you place this macro in the Sheet module in the VB editor (Sheet1, or whatever sheet you are working on), it will happen automatcially whenever anything is entered into the cells.

I wrote it to work on column G. Modify to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
'   This does column G (the seventh column)
    If Target.Column = 7 Then
        If Target.Value < 0 Then
            MsgBox "Cannot enter a value less than zero"
            Application.Undo
        Else
            Target.Value = Round(Target.Value / 2, 1)
        End If
    End If
        
    Application.EnableEvents = True

End Sub

 

[tnoe]

Ok, I am not that familiar with macros. Is there an IF formula that could make this work?

Tracey

 

[Mudface]

Not if you want the division to occur in the same cell, no.

 

[Joe4]

This is the only way you can control the data as it is entered.

If they enter the value in column A1, you could do conditional formatting to ensure that it is not less than zero. However, you can not have the entry be divided by two as it is entered without the use of a macro. In B1, you could enter = A1/2, which would divide it by 2, but you still would have the original amount in A1.

 

[tnoe]

I can put the formula in the cell 1b where it would change 1a.

I want it to say: If 1a has value then make it divisible by 2. I just do not know how to word that for the formula to work.

Tracey

 

[Joe4]

=IF(A1>0,A1/2,"")

 

[tnoe]

I don't think that worked. Thank you for your help. I will just have to do something else.

Tracey

 

[Joe4]

As Mudface and I said earlier, you cannot change the value of what is being entered into a cell without the use of a macro. The formula that I gave you, if entered into cell B1, just looks at what is entered into cell A1 and does the math on it if the value is greater than 0. It will not change what is in A1.

If you want to divide a whole column by 2, and overwrite the original values, you can do that using Copy | Paste Special | Divide.