Replace less than 0 with 0 in column

[KingTamo]

Hi experts
column t has values negative (less than zero). How can I replace those with zero without looping?

[TMS]

You cold use Autofilter. Filter column T on negative values and replace (all) visible cells with 0.

[KingTamo]

Thanks. I wanted a line of code

[jolivanes]

Try

Please Login or Register  to view this content.

[TMS]

Maybe this:

Please Login or Register  to view this content.

[KingTamo]

Thank you so much. this line is very great

[TMS]

Try

Please Login or Register  to view this content.

OK, that's a bit shorter than mine

[KingTamo]

@jolivanes
how can I color these cells?

[jolivanes]

Use Trevor's suggestion of AutoFilter and color visible cells any color your heart desires.

[TMS]

this:

Please Login or Register  to view this content.

[KingTamo]

Thank you so much both of you

[jolivanes]

Or (assuming no formulae in Column T)

Please Login or Register  to view this content.

[TMS]

Or (assuming no formulae in Column T)

Please Login or Register  to view this content.

That's outside the box!

[jolivanes]

Hi Trevor.
I am glad you didn't say "outside the closet"!!!! (like coming out of the closet)
Thanks though

[KingTamo]

Can the same outside the box be applied to the condition
(cells that are less than 2000)?

[jolivanes]

What was the outcome when you tried it?
I would say that you'll be looking at a loop. Which is OK

Have you tried Trevor's solution from Post #10? Change the less than 0 to any number you want to use.

[KingTamo]

Yes it worked for looping, but I told myself that ideas outside box has its word

[Winon]

Hi guys,

Could this not be simply done with Conditional Formatting?

Just wondering.

[KingTamo]

Will conditional formatting change for example -125 to 0?

[TMS]

Will conditional formatting change for example -125 to 0?

No. CF does what it says on the tin, Conditional Formatting. It can't change the content of a cell, just the way it is displayed.

[KingTamo]

Thank you so much TMS sir

[TMS]

Note: you CAN change the way the negative numbers are displayed using a CUSTOM format:

Please Login or Register  to view this content.

It doesn't CHANGE the value in the cell, just displays it as 0.

[KingTamo]

Yes but I found the total changed, so this is only apparent change not real change

[TMS]

Yes but I found the total changed, so this is only apparent change not real change

Don't understand this comment.

Neither Conditional Formatting, nor a Custom Format, will change the value in the cell. You need one of the solutions you have already been given.