Andy2483

¦^´_ 10# °a¤ªºµ


    ÁÂÁ«e½ú¦A´£¨Ñ±¡¹Ò½d¨Ò
«á¾Ç²q´ú±¡¹Ò¬O:
¿é¤J«e:

¤U¸ü (72.67 KB)

2022-11-23 10:05

¦U­ÓÀx¦s®æ¿é¤J«á:

¤U¸ü (84.2 KB)

2022-11-23 10:06

«á¾Ç«ØijIJµo¤è¦¡,½Ð¦U¦ì«e½ú«ü¾É!ÁÂÁÂ

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim A&, Ti#, Ts$
With Target
   If (.Row >= 7 And .Row <= 17 And .Row Mod 3 = 1) And (.Column >= 7 And .Column <= 16) And .Count = 1 Then
      A = .Value
      If (A < 48 Or A > 50) And (A <> 0 Or .Value <> "") Then
         .Interior.Color = RGB(255, 255, 0)
         Ti = (50 - A) / 1000
         Ts = Format(Ti, "0.000")
         .Offset(1, 0) = IIf(Ti > 0, "+" & Ts, Ts)
         .Offset(1, 0).Interior.Color = RGB(170, 240, 190)
         Else
            .Interior.ColorIndex = xlNone
            .Offset(1, 0).Interior.ColorIndex = xlNone
            .Offset(1, 0) = ""
      End If
   End If
End With
Application.EnableEvents = True
End Sub

Andy2483

¦^´_ 10# °a¤ªºµ


    ÁÂÁ«e½ú
«á¾ÇÂǦ¹½d¨Ò½m²ß ¦Û­q¸q¨ç¼Æ,Æ[©À¤£ª¾¬O§_¹ï?? Àµ½Ð¦U¦ì«e½ú«ü¾É!ÁÂÁÂ

[G8]Àx¦s®æ¿é¤J =GetRangeStep(48,50,G7)  :

¤U¸ü (128.86 KB)

2022-11-23 16:42

[G7]...¿é¤J¼Æ¦rµ²ªG:

¤U¸ü (88.91 KB)

2022-11-23 16:44

Option Explicit
Function GetRangeStep(Mi As Long, Mx As Long, xArea As Range)
'¦Û­q¸q¨ç¼Æ GetRangeStep(³Ì¤p­È,³Ì¤j­È,­pºâªºÀx¦s®æ)
'[G8]Àx¦s®æ¿é¤J =GetRangeStep(48,50,G7)
'[G8]Àx¦s®æ½Æ»s¤½¦¡¨ì¨ä¥LÀx¦s®æ
Dim xR As Range, xV$, Ti#, Ts$
Application.Volatile
If (xArea.Value < Mi Or xArea.Value > Mx) And xArea.Value <> "" Then
   Ti = (50 - xArea) / 1000
   Ts = Format(Ti, "0.000")
   xV = IIf(Ti > 0, "+" & Ts, Ts)
   Else
      xV = ""
End If
GetRangeStep = xV
End Function

¼B¤j­G

Hello, °a¤ªºµ ¤j

­¶­±::[01]
(1) ¤¤¤ß­È¬°50  --> F5=50
(2) ¥[´î­È¤½¦¡ --> G8= IF(G7="","",IF($F$5>=G7,"+","") & Text(($F$5-G7)*0.001,"0.000"))

­¶­±::[02]
(1) ¤¤¤ß­È¬°10  --> B1=10
(2) ¥[´î­È¤½¦¡ --> C5=IF(OR(C4="",$B$1=C4),"",IF($B$1>=C4,"+","") & TEXT(($B$1-C4)*0.001,"0.000"))

¥H¤W, ¸Ì­±ªºÃC¦â, ¥i¥H¥Î"³]©w®æ¦¡¤Æªº±ø¥ó" ¥\¯à¥h©w¸q. ©Ò¥H¥u­n«O¯d²M°£¥\¯àÁä, ¦Ó¥\¯àÁä¸Ì¤]¥u­n°µÂI¦ì¤Î¹ê»Ú­Èªº¤º®e­È²M°£§Y¥i.

Thanks ~

°a¤ªºµ

·PÁ ¼B¤j­G  ©M Andy2483  ¤j¤jªºÀ°¦£   ³o¼Ë¦n¦h¤F ÁÂÁÂ