• 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.

finding difference between two cells

sorry still the error code is same
kindly provide mod function to the grid
based on the previous attachment i have provided the data and the statements
thank you
 

p45cal

Well-Known Member
There is an error when i hit the button in the #47 provided attachment
it says run time error 1004
application defined or object defined error
Please follow instruction in post #44 detailing what you shoud do before clicking the button to update the conditional formatting.
 
thank you so much for your support
how to combine these functions


=MOD(LEFT(I1)-LEFT(H1),10)
and
=MOD(RIGHT(I1)-RIGHT(H1),10)

=MOD(LEFT(I1)-RIGHT(H1),10)
and
=MOD(RIGHT(I1)-LEFT(H1),10)
 

Attachments

hi can you please let whether i am being blocked or not because there is no reply to my post
kindly let me know if any mistakes were done by me
 

vletm

Excel Ninja
maniniinfotech
Have You followed and noted these:
eg Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight.
 

vletm

Excel Ninja
maniniinfotech
You've skipped many times my questions
... which could help You!
Those lines seems to be important ... but
... without any answers nor logic ... those will be a challenge too.
If You every time change Your details or not ... then You can learn to wait.
I've see that jpeg - it looks same ... only name has changed.
Below, my sample with previous files data; one cell has four colors and value.
Screenshot 2019-08-31 at 18.24.07.png
 

p45cal

Well-Known Member
I cannot understand the colour coding required from either the workbook attached to msg#61 (the conditional formatting is all over the place) or from the picture. Not a hope.
can it be done inside of cell rather than borders
like previously given image
giving shapes inside the the cell
The attached is vletm's file with additional sheets:
Sheet3 is vletm's original border-colouring solution
Sheet4 uses an adapted version of vletm's code but places shapes on each cell instead, keeping vletm's colour coding. You can compare the results on Sheet3 and Sheet4 easily.
Sheet5 is where I've taken out the extra rows since they're not needed for colouring anymore.

This means you'll be able to keep the conditional formatting for comparing two tables, and you no longer need the conditional formatting for the 4 cells around each number.

There's a little flexibility to adjust how the shapes are placed on the sheet:
1. The thickness of each line can be adjusted; in Module 1, macro blah, there's a line:
Thickness = Application.Min(.Width, .Height) * 0.2
The 0.2 here means 20% of the height or width of the cell, whichever is the smaller.
When the value is .05 (=5%) you get:
62478

When it's 0.3 (=30%) you get:
62479

2. You can move the shapes away from the edges of the cell to introduce a bit of (currently white) space between cells.
There's a line just below the one mentioned in (1.) above:
ofset = 0
When this has a value of zero, the shape goes right to the edge of the cell as in the pictures above.
When the value is 1 you get:
62480

Each time the button is clicked, some 300 shapes get added to the sheet; if they're not removed before clicking the button again the numbers of shapes on a sheet can quickly get into the thousands, so there's another button to remove them.
 

Attachments

vletm

Excel Ninja
maniniinfotech
... [ Do It ] ...
if You refer to my file then
... You should paste data with same layout as in a sample file
and data should be something same kind as in a sample file.
 
I cannot understand the colour coding required from either the workbook attached to msg#61 (the conditional formatting is all over the place) or from the picture. Not a hope.

The attached is vletm's file with additional sheets:
Sheet3 is vletm's original border-colouring solution
Sheet4 uses an adapted version of vletm's code but places shapes on each cell instead, keeping vletm's colour coding. You can compare the results on Sheet3 and Sheet4 easily.
Sheet5 is where I've taken out the extra rows since they're not needed for colouring anymore.

This means you'll be able to keep the conditional formatting for comparing two tables, and you no longer need the conditional formatting for the 4 cells around each number.

There's a little flexibility to adjust how the shapes are placed on the sheet:
1. The thickness of each line can be adjusted; in Module 1, macro blah, there's a line:
Thickness = Application.Min(.Width, .Height) * 0.2
The 0.2 here means 20% of the height or width of the cell, whichever is the smaller.
When the value is .05 (=5%) you get:
View attachment 62478

When it's 0.3 (=30%) you get:
View attachment 62479

2. You can move the shapes away from the edges of the cell to introduce a bit of (currently white) space between cells.
There's a line just below the one mentioned in (1.) above:
ofset = 0
When this has a value of zero, the shape goes right to the edge of the cell as in the pictures above.
When the value is 1 you get:
View attachment 62480

Each time the button is clicked, some 300 shapes get added to the sheet; if they're not removed before clicking the button again the numbers of shapes on a sheet can quickly get into the thousands, so there's another button to remove them.




if do the conditional formatting then the color is filled inside of the cell also rather than to the borders
with previously given mod function =mod(a1-h10,10)=0
 

vletm

Excel Ninja
maniniinfotech
Is there ... ) Many things are possible, but without answers there are challenges.
If do the condi... ) Seems that You don't know, what do You need! You ask this - continue with that - next time this & that ... zero is 0.
 

vletm

Excel Ninja
maniniinfotech
As I wrote in my previous reply ... did You read those?
# no answers = a challenge
# conditional formatting = I don't use ... I did my version as You let understand in past.
 
Top