Excelでは条件次第でセルの色を変えることができます。条件付き書式から変えることができますが、VBAでも変えることができます。
変動率が0％より低いと赤色、0%を無色、0%より高いと緑色と設定します。
下限を-100%、上限を100%とします。
下限か上限に近づくにつれて色が段階的に濃くなるようグラデーションの設定にします。
Sub Crypto_JSON_In_Excel()
Dim CryptoCurrencyJSON As Object, CryptoCurrencyNode
Dim WinHttpReq As Object, TickerJSON As String
Dim tickerSheet As Worksheet, arr(10) As Variant
Set tickerSheet = ThisWorkbook.Sheets(1)
arr(1) = "name"
arr(2) = "symbol"
arr(3) = "price_jpy"
arr(4) = "price_btc"
arr(5) = "24h_volume_jpy"
arr(6) = "market_cap_jpy"
arr(7) = "available_supply"
arr(8) = "percent_change_1h"
arr(9) = "percent_change_24h"
arr(10) = "percent_change_7d"
Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
WinHttpReq.Open "GET", "https://api.coinmarketcap.com/v1/ticker/?convert=JPY&limit=50", False
WinHttpReq.send
TickerJSON = WinHttpReq.responseText
Set WinHttpReq = Nothing
Set CryptoCurrencyJSON = ParseJson(TickerJSON)
For i = 1 To 10
tickerSheet.Cells(1, i) = arr(i)
Next i
trow = 2
For Each CryptoCurrencyNode In CryptoCurrencyJSON
For i = 1 To 10
tickerSheet.Cells(trow, i) = CryptoCurrencyNode(arr(i))
Next
trow = trow + 1
Next
With Range(Cells(2, i - 3), Cells(trow - 1, i - 1))
.FormatConditions.AddColorScale ColorScaleType:=3
.FormatConditions(Range(Cells(2, i - 3), Cells(trow - 1, i - 1)).FormatConditions.Count).SetFirstPriority
.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(1).Value = -100
.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = 7039480
.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(2).Value = 0
.FormatConditions(1).ColorScaleCriteria(2).FormatColor.ThemeColor = xlThemeColorDark1
.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(3).Value = 100
.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = 8109667
End With
End Sub
●実行結果
変動率がプラスのものは緑、マイナスのものは赤となりました。
