vijay vj
asked on
How to change the format as number
Dear sir,
I have vba formula to update data in excel, but i need to change the format , as its shows many typo error so kindly help me.
1,00,000 value as green colour and below -1,00,000 value as red , but while updating the colour are not showing, while i double click and enter the cell , its shows typo error and asking do you accept correction, when i click yes , the colour changes, even though i changed the format its not working.
I have vba formula to update data in excel, but i need to change the format , as its shows many typo error so kindly help me.
Worksheets("NF").Cells(28, 15).Value = changeinOpenInterest
the value will be 1,00,000 or 2,00,000 or -2,00,000 , it may be + or - value , the problem is that when i have created a rule that above 1,00,000 value as green colour and below -1,00,000 value as red , but while updating the colour are not showing, while i double click and enter the cell , its shows typo error and asking do you accept correction, when i click yes , the colour changes, even though i changed the format its not working.
ASKER
I have attached my excel kindly go through column "O" , and check the values of negative one. , sir.
NIF.xlsm
NIF.xlsm
What do you want the negative values to look like?
Please find attached
NIF.xlsm
NIF.xlsm
ASKER
Sir, i need value of negative below one lakh should be red and value of above one lakh should be green
Vijay,
In your attached file there was no value less than 100,000. I just changed the value of O12 and set the rule to see if rule applies. Check in attached...
NIF.xlsm
In your attached file there was no value less than 100,000. I just changed the value of O12 and set the rule to see if rule applies. Check in attached...
NIF.xlsm
ASKER
https://ibb.co/b2T0gF
https://ibb.co/j6Msvasir kindly check before and after image after i have update i have double click and the values turn red in O column, i have encircled
Please find attached.
NIF.xlsm
NIF.xlsm
ASKER
even i have tried that before it not worked sir, i think in vba that format has to be changed.
Worksheets("NF").Columns("O"). _
NumberFormat = "0.00"
i have tried this but no use.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
working perfectly sir, but i have to apply it for each row stock sir.......
Why? above code is for whole Column O
ASKER
thanks sir, mind blowing with your excel vba knowledge, thanks for your kind help
Thanks for showing the way to solve it. Yeah, I admire your knowledge so much!
You're Welcome Vijay! Glad I was able to help.
Thanks for the comment :)
Thanks for the comment :)
Open in new window
Then there is this which gives a longer list of NumberFormats
What you are looking for might be something like this:
Open in new window
Though you might have to change the . with whatever your version of Excel uses.