# finding difference between two cells

#### maniniinfotech

##### Member
am not able to tell u what is my requirement exactly through words

#### Attachments

• 9.4 KB Views: 2

#### p45cal

##### Well-Known Member
A guess. See conditional formatting in ranges A1:F10 and H1:M10 of the attached.

#### Attachments

• 9.4 KB Views: 4

#### maniniinfotech

##### Member
A guess. See conditional formatting in ranges A1:F10 and H1:M10 of the attached.
it is working awesomely thank you so much, but i do have another requirement

#### maniniinfotech

##### Member
requirement (1)Is there possibility of giving four colors inside of a cell,

requirement (2)I can give you the if statements to which you have to convert them to mod formula

#### Attachments

• 85.3 KB Views: 5

#### maniniinfotech

##### Member
can you provide same to the given attachment

#### maniniinfotech

##### Member
can you convert the given data to the now giving format

#### Attachments

• 105.7 KB Views: 13

#### Peter Bartholomew

##### Well-Known Member
Is this close to meeting your requirement?

#### Attachments

• 12.6 KB Views: 9

#### p45cal

##### Well-Known Member
In your COLOUR CODE 3 sheet you have many formulae of the ilk:
=IF(RIGHT(P16,1)>=LEFT(P16,1)+0,RIGHT(P16,1)+0,(RIGHT(P16,1)))
which puts either text or numeric result in the cell, depending on whether the first digit is larger thanthe last digit; Why?

#### maniniinfotech

##### Member
Is this close to meeting your requirement?
hi peter differences are correct but based on that difference value it should highlight the both cells from which output has come

#### maniniinfotech

##### Member
In your COLOUR CODE 3 sheet you have many formulae of the ilk:
=IF(RIGHT(P16,1)>=LEFT(P16,1)+0,RIGHT(P16,1)+0,(RIGHT(P16,1)))
which puts either text or numeric result in the cell, depending on whether the first digit is larger thanthe last digit; Why?

is there any option based on numeric value those option work because in the given attachment it only works on text format

#### maniniinfotech

##### Member
is there any option based on numeric value those option work because in the given attachment it only works on text format
i just require the last digit only

#### maniniinfotech

##### Member
A guess. See conditional formatting in ranges A1:F10 and H1:M10 of the attached.
hi am unable to apply same to the other sheet can you provide me the same to the given data and format

#### Attachments

• 15.1 KB Views: 2

#### maniniinfotech

##### Member
Is this close to meeting your requirement?
PLEASE CHECK THE ATTACHMENT WORK HAS BEEN DONE

#### Attachments

• 17.9 KB Views: 5

#### maniniinfotech

##### Member
HI CAN YOU CHECK THE GIVEN ATTACHMENT
CAN YOU WRITE A SAME TYPE OF FORMULA TO THE TWO DIGITS INSIDE A CELL AS SAME AS NOW GIVEN
AND FOR THE TWO DIGITS DON CHANGE THE CELL BACKGROUND INSTEAD CHANGE THE FONT COLOR OF THE RIGHT AND LEFT

#### Peter Bartholomew

##### Well-Known Member
Sorry I didn't refresh before posting the last time so didn't know the question as posed had been answered and the problem moved on.
Anyway, since I have it, this completes my solution to first part of the problem.

#### Attachments

• 20.9 KB Views: 4

#### maniniinfotech

##### Member
HI PETER THANK YOU SO MUCH FOR THE OUTPUT AND KINDLY LOOK INTO PREVIOUS POSTS OF MINE AND FIND SOMETHING TO HELP ME

#### vletm

##### Excel Ninja
@maniniinfotech
Push once < Caps Lock >
• PLEASE DON'T SHOUT! We have big ears and will hear you just the same.

#### p45cal

##### Well-Known Member
hi am unable to apply same to the other sheet can you provide me the same to the given data and format
In the attached is conditional formatting as I think you want it.

I have also added a macro to apply conditional formatting quickly, accurately and flexibly. There's a button on both sheets which calls the macro blah.
Before clicking the button, you must first select two areas on the sheet where you want the conditional formatting to be added; start by selecting the first area normally, then while holding the Ctrl key on the keyboard down, select the second area, then let go. Then you can click the button which:
1. Looks at the colour mapping on Sheet2 cells W1:X10 (you can change the colours in column X to suit yourself).
2. Clears all conditional formatting from your two selected areas.
3. Applies new conditional formatting to those same areas.

Conditional formatting on a sheet can get out of control - it can get resource hungry if there's a lot of it. It's very easy to keep on adding conditional formatting and ultimately it becomes a mess. It's a good idea to keep it under control by removing unecessary conditional formatting.

#### Attachments

• 35.4 KB Views: 9

#### maniniinfotech

##### Member
Thank you so much for your help it is working 100% accurately

#### maniniinfotech

##### Member
what is the option to convert if statement to mod function
1>=(IF(LEFT(D2,1)>=LEFT(C2,1),LEFT(D2,1)-LEFT(C2,1),LEFT(D2,1)+10-LEFT(C2,1))&(IF(RIGHT(D2,1)>=RIGHT(C2,1),RIGHT(C2,1)-RIGHT(C2,1),RIGHT(D2,1)+10-RIGHT(C2,1))))

2>=(IF(LEFT(D2,1)>=RIGHT(C2,1),LEFT(D2,1)-RIGHT(C2,1),LEFT(D2,1)+10-RIGHT(C2,1))&(IF(RIGHT(D2,1)>=LEFT(C2,1),RIGHT(D2,1)-LEFT(C2,1),RIGHT(D2,1)+10-LEFT(C2,1))))

these two are to be converted to mod, is there any option kindly teach me

#### Peter Bartholomew

##### Well-Known Member
I do detest the normal spreadsheet convention of working with relative cell references!
It serves to emphasis the irrelevant whilst obscuring any significance the data may have.

To demonstrate the difference, I have chosen a 6x5 block of numbers from one of @maniniinfotech's workbooks and called it 'Grid'.
I then split out the two digits from each cell, creating two 6x5 arrays 'X' and 'Y'. These refer to
= QUOTIENT(+Grid, 10) and
= MOD(+Grid, 10)
respectively. The '+' signs coerce characters into numbers in case the original grid is comprised of text strings.

The derived tables are then given by
= MOD( X+Y, 10 ) and
= MOD( Y- X, 10 )
The formulas are array formulas so need to be committed with CSE in most versions of Excel (dynamic array versions excepted).

The algebraic simplification could also be made in the direct referencing versions of the formulas
=IF(LEFT(C18,1)+RIGHT(C18,1)>=10,LEFT(C18,1)+RIGHT(C18,1)-10,(LEFT(C18,1)+RIGHT(C18,1)))
=IF(RIGHT(C18,1)>=LEFT(C18,1),RIGHT(C18,1)-LEFT(C18,1),(RIGHT(C18,1)+10-LEFT(C18,1)))

but the notation itself tends to obscure any algebraic relationships within the formulas.

1. Dan Brickin correctly observed that defining variables is 'tedious'
2. Spreadsheets are designed for users for whom 'algebra' is a nightmare left over from schooldays
3. The overwhelming majority of spreadsheets do not have any formulas so crap notation is not an issue

#### Attachments

• 30.6 KB Views: 5
Last edited:

#### maniniinfotech

##### 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

#### maniniinfotech

##### Member
hi in the given attachment there are if statements kindly check and please provide me the mod functions

#### Attachments

• 30.9 KB Views: 2

#### Peter Bartholomew

##### Well-Known Member
Sorry, I hadn't tested the VBA. As I had moved the reference colour array (I had deleted its original location) the code no longer picked it up correctly. I have now introduced a further defined Name 'CFcolours' that ensures it picks up the reference table. I also found it confusing having a sheet with the codename 'Sheet1' and tab name 'Sheet2' so I changed that to 'test'.

Please do not interpret my earlier post as a personal attack; in no way was that my intention. I like your idea of using a macro to introduce the conditional formats and I can hardly blame you for the way spreadsheets work.

#### Attachments

• 24.2 KB Views: 6