Data with Different score

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
279
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I have a Wave 2 and Wave 1 data and need different only if we have any letter in Column A and B in Column D for example i have kept data on Column F.

FS1.xlsx
ABCDEF
1WAVE 1 (A)WAVE 2 (B)Diff.Difference Between (Wave 2 - Wave 1) (Need in this format)
253B14#VALUE!14 (-39)
32224A#VALUE!24 (+2)
421B15#VALUE!15 (-6)
51011A#VALUE!11(+1)
64342-1
776-1
81843A#VALUE!43 (+25)
Sheet4
Cell Formulas
RangeFormula
D2:D8D2=B2-A2



Thanks in Advance
Regards,
Sanjeev
 

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
279
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I trust not as that would be a breach of #4 of the Forum Rules

Here is a macro that will calculate the results and format the part in parentheses.
Please check the code for the column that the results should go in. The code current puts them in column F but is easily where where noted in the code.
One thing that you did not make clear is what should happen if the number part of both columns A & B are exactly the same?

VBA Code:
Sub Get_Results()
  Dim rw As Range
  Dim a As Long, b As Long, P As Long, L As Long
 
  Application.ScreenUpdating = False
  For Each rw In Range("A2", Range("B" & Rows.Count).End(xlUp)).Rows
    If Not IsNumeric(rw.Cells(1).Value) Or Not IsNumeric(rw.Cells(2).Value) Then
      a = Val(rw.Cells(1).Value)
      b = Val(rw.Cells(2).Value)
      With Intersect(rw.EntireRow, Columns("F"))  '<- Check what column results are to go in
        .Value = b & " (" & IIf(b - a >= 0, "+", "") & b - a & ")"
        P = InStr(1, .Value, "(")
        L = InStr(1, .Value, ")") - P + 1
        .Font.ColorIndex = xlAutomatic
        .Characters(P, L).Font.Color = IIf(InStr(.Value, "-"), vbRed, vbBlue)
      End With
    End If
  Next rw
  Application.ScreenUpdating = True
End Sub

Here are my results

View attachment 39985


Thanks you so much sir :) it work very Well in excel file. it work very easy for me now i can just copy PPT with color

Just one more thing sir
Can we change RGB as per requirement as now we only change the color code.

".Characters(P, L).Font.Color = IIf(InStr(.Value, "-"), vbRed, vbBlue)"


Thanks you sir for you help on this

Regards,
Sanjeev
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,850
Office Version
  1. 365
Platform
  1. Windows
I trust not as that would be a breach of #4 of the Forum Rules
@Peter_SSs I haven't received anything via direct message, although I was thinking this thread started off as needing a formula and morphed into a need for specific formatting and output!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,244
Office Version
  1. 365
Platform
  1. Windows
Thanks you so much sir :) it work very Well in excel file. it work very easy for me now i can just copy PPT with color
Good news!


Just one more thing sir
Can we change RGB as per requirement as now we only change the color code.

".Characters(P, L).Font.Color = IIf(InStr(.Value, "-"), vbRed, vbBlue)"
Sure, you can change the vbRed and vbBlue to any RGB colours that you want.
 

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
279
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good news!



Sure, you can change the vbRed and vbBlue to any RGB colours that you want.


Sure sir I have changed the color with "vbRed" but RGB is not exact.

for example i need RGB for RED (R=192,G=0,B=0) or sometime different RGB .

Let me know Sir if it work:)

Thank you.
Sanjeev
 

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
279
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So, can't you just use RGB(192,0,0) where I have used vbRed?


What does this mean? Use whatever RGB you want as I don't know what colour you want. :)

like in Red color we have lot of variant if we change in RGB number so. Can we have macro with that option like i can change RGB as per my condition :)

i.e now i need RGB (192,10,12) or later i can change RGB to (192,55,30)

Hope you got it :)

Thanks.
Sanjeev
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,244
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

now i need RGB (192,10,12) or later i can change RGB to (192,55,30)
How would we know what RGB values you want at any particular time?
 

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
279
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How would we know what RGB values you want at any particular time?

Sir like i will only 2 condition +/- will show Blue or Red for RED i will different color

Could you please see attached file for ref. :)
 

Attachments

  • 001.png
    001.png
    39.5 KB · Views: 3

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,244
Office Version
  1. 365
Platform
  1. Windows

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
279
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The image appears to show RGB(192,10,20) so as I said before ..



Rich (BB code):
.Characters(P, L).Font.Color = IIf(InStr(.Value, "-"), vbRed, vbBlue)
.Characters(P, L).Font.Color = IIf(InStr(.Value, "-"), RGB(192, 10, 20), vbBlue)


Awesome Sir :) :)

I was looking same for it..

Thanks you once again sir :)

Have a great day ahead!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,832
Messages
5,661,200
Members
418,620
Latest member
JUrq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top