• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

When I apply conditional formatting formula disappears

Jack-P-Winner

Jack-P-Winner

Member
I have cells that are all color coded and very important that they are. For some reason when I copy and paste numbers from one sheet to the next or from a web site. I have to run a Macro to "fix" them because the CF will not work "at all" I am not sure of a couple things.

1. Is the format of the numbers incorrect?
2. Can my Macro be fixed to prevent it from taking away my CF when it runs?

This has been a nuisance for a long time. I've given you two example, the example off to the right you can run the Macro that says FIX NUM and you will the formula disappears after the color code is applied. The CF always seems to work under SET 1 but not SET 2

Here is the Macro and I uploaded an example and thank you for your help once again

Code: 
Sub changetonumber()
Dim cell As Range, s As Variant
For Each cell In Selection
  s = Trim(cell.Value)
  If IsNumeric(s) Then
    cell.Value = CInt(s)
  End If
Next
End Sub



Thanks
 

Attachments

  • Houston we have a problem.xlsm
    30.8 KB · Views: 1
Your code is setting the cells value Cell.value to a value CInt(s) in the line:
cell.Value = CInt(s)

so of course it looses its formula

What do you actually want to do?
 
Hiya Jack,
It seems that your macro simply changes numeric values to integers. This will replace the contents of the cell (formula) with the integer value (just a number) like Hui said.
If you just want to ensure that the numbers in these cells are integers, then you can just modify your formula like this:

Code: 
Your current formula:
=IF(LEN(AI5)=1,RIGHT(AI5+5,1),IF(RIGHT(LEFT(AI5,1)+5,1)="0","",RIGHT(LEFT(AI5,1)+5,1))&RIGHT(RIGHT(AI5,1)+5,1))

New formula:
=INT(IF(LEN(AI5)=1,RIGHT(AI5+5,1),IF(RIGHT(LEFT(AI5,1)+5,1)="0","",RIGHT(LEFT(AI5,1)+5,1))&RIGHT(RIGHT(AI5,1)+5,1)))

This will remove the need for a macro at all and ensure that the values are rounded to the nearest integer.

Hope this helps!
 
Hi Larry ,

Your CF rules are all checking to see whether the cells have numeric values in them ; based on their values , they are being colored.

Your formulae are all assigning text values , which is why your CF rules will not recognize them ; hence you are trying to convert the text values in the cells to numbers !

Why not do either of the following ?

1. Change all your CF rules to check for text values instead of numeric values.

For example , one rule is :

Cell Value = 9

Change this to : Cell Value = "9"

2. Change all your formulae to assign numeric values instead of text values.

For example , one formula is :

=IF(LEN(Y5)=1,RIGHT(Y5+5,1),IF(RIGHT(LEFT(Y5,1)+5,1)="0","",RIGHT(LEFT(Y5,1)+5,1))&RIGHT(RIGHT(Y5,1)+5,1))

First , I do not know under what conditions the second half of the formula will be executed , since the length of Y5 and all other cells in columns Y , Z , AA and AB is 1.

Thus , the portion of the IF statement colored BLUE will never be executed.

As far as I can see , the formula can be changed to :

=RIGHT(Y5+5,1)+0

Narayan
 
Stevie said:
Hiya Jack,
It seems that your macro simply changes numeric values to integers. This will replace the contents of the cell (formula) with the integer value (just a number) like Hui said.
If you just want to ensure that the numbers in these cells are integers, then you can just modify your formula like this:

Code: 
Your current formula:
=IF(LEN(AI5)=1,RIGHT(AI5+5,1),IF(RIGHT(LEFT(AI5,1)+5,1)="0","",RIGHT(LEFT(AI5,1)+5,1))&RIGHT(RIGHT(AI5,1)+5,1))

New formula:
=INT(IF(LEN(AI5)=1,RIGHT(AI5+5,1),IF(RIGHT(LEFT(AI5,1)+5,1)="0","",RIGHT(LEFT(AI5,1)+5,1))&RIGHT(RIGHT(AI5,1)+5,1)))

This will remove the need for a macro at all and ensure that the values are rounded to the nearest integer.

Hope this helps!
Click to expand...

This seems to do the trick. THANK YOU. will do a test run
 
NARAYANK991 said:
Hi Larry ,

Your CF rules are all checking to see whether the cells have numeric values in them ; based on their values , they are being colored.

Your formulae are all assigning text values , which is why your CF rules will not recognize them ; hence you are trying to convert the text values in the cells to numbers !

Why not do either of the following ?

1. Change all your CF rules to check for text values instead of numeric values.

For example , one rule is :

Cell Value = 9

Change this to : Cell Value = "9"

2. Change all your formulae to assign numeric values instead of text values.

For example , one formula is :

=IF(LEN(Y5)=1,RIGHT(Y5+5,1),IF(RIGHT(LEFT(Y5,1)+5,1)="0","",RIGHT(LEFT(Y5,1)+5,1))&RIGHT(RIGHT(Y5,1)+5,1))

First , I do not know under what conditions the second half of the formula will be executed , since the length of Y5 and all other cells in columns Y , Z , AA and AB is 1.

Thus , the portion of the IF statement colored BLUE will never be executed.

As far as I can see , the formula can be changed to :

=RIGHT(Y5+5,1)+0

Narayan
Click to expand...

Thanks Narayan,

I can finally take some time and come back to this. This works fine for formula that I have in my cells but what is happening is when I paste numbers with no formula in a cell they will not hi lite either. When I go back into my CF and change say the =9 to ="9" all my colors go back to no color. Here is an example attached. Look at columns I A AB AK. They did not color when I pasted them. Also, I can run a Macro that will search for certain strings and place the results on a new sheet and the same rows will not be hi lilted but they are hi lilted on the original sheet

Thanks for looking at this
 

Attachments

  • Narayan CF color.xlsx
    72.2 KB · Views: 0
Hi Larry ,

The problem with all the problem cells is that they are not numbers ; all of them have 2 trailing space characters.

How have these additional spaces got into these cells ? Can you eliminate them ? If not , the CF formula has to be modified to consider these cases.

Narayan
 
hmm, I am not sure what space characters are but will look in each cell to see what I can find. All I do is copy from the txlottery.org web site and paste to a spreadsheet. I go to their page that allows you to print to a printer. That way when you paste into excel everything is already in columns and rows. Evidentially, it is leaving these space characters in a coupe of the columns
 
Hi Larry ,

You mentioned columns I , AB and AK in your earlier post ; just go to any cell in any of these ranges :

I2 : I10 , I15 : I23

AB2 : AB10 , AB15 : AB23

AK2 : AK10 , AK15 : AK23

Press F2 to enter EDIT mode ; you should see the cursor 2 characters away from the digit ; press backspace to remove these two additional characters , and press ENTER. You should see the cell colored according to the CF rules.

You have to decide what to do ; if you are always going to receive these additional characters during the copy + paste , then you need to revise the CF rules to consider these two additional characters , and still do what you want them to do.

Narayan
 
NARAYANK991 said:
Hi Larry ,

You mentioned columns I , AB and AK in your earlier post ; just go to any cell in any of these ranges :

I2 : I10 , I15 : I23

AB2 : AB10 , AB15 : AB23

AK2 : AK10 , AK15 : AK23

Press F2 to enter EDIT mode ; you should see the cursor 2 characters away from the digit ; press backspace to remove these two additional characters , and press ENTER. You should see the cell colored according to the CF rules.

You have to decide what to do ; if you are always going to receive these additional characters during the copy + paste , then you need to revise the CF rules to consider these two additional characters , and still do what you want them to do.

Narayan
Click to expand...

WOW! That is too weird, it is only the last row of each set of numbers. For the Pick 3 its the 3rd and Pick 4 its the 4th. It would be a pain in the tail to do each cell for 2000+ rows and nonsense to adjust in CF too would it not? Is there a way to write a Macro to remove the back spacing by 2 spaces for me to run on jus those columns?
 
This Macro?

Code: 
Code (vb):

Sub changetonumber()
Dim cell As Range, s As Variant
For Each cell In Selection
  s = Trim(cell.Value)
  If IsNumeric(s) Then
    cell.Value = CInt(s)
  End If
Next
End Sub
 
You must log in or register to reply here.
Back
Top