Data with Different score

[sksanjeev786]

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
	A	B	C	D	E	F
1	WAVE 1 (A)	WAVE 2 (B)		Diff.		Difference Between (Wave 2 - Wave 1) (Need in this format)
2	53B	14		#VALUE!		14 (-39)
3	22	24A		#VALUE!		24 (+2)
4	21B	15		#VALUE!		15 (-6)
5	10	11A		#VALUE!		11(+1)
6	43	42		-1
7	7	6		-1
8	18	43A		#VALUE!		43 (+25)
Sheet4

Cell Formulas
Range	Formula
D2:D8	D2	=B2-A2

Thanks in Advance
Regards,
Sanjeev

 

[sksanjeev786]

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?

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

 

[JackDanIce]

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]

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]

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

 

[Peter_SSs]

for example i need RGB for RED (R=192,G=0,B=0)

So, can't you just use RGB(192,0,0) where I have used vbRed?

or sometime different RGB .

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

 

[sksanjeev786]

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]

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]

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.

 

[Peter_SSs]

Could you please see attached file for ref.

The image appears to show RGB(192,10,20) so as I said before ..

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

just use RGB(_,_,_ )where I have used vbRed

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

 

[sksanjeev786]

The image appears to show RGB(192,10,20) so as I said before ..

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