Hello I get some data from website using custom function with an XMLHTTP request now I need to apply some formatting condition to copied data and I would appreciate some advice using VBA :
你好，我使用XMLHTTP请求的自定义函数从网站上获得一些数据，现在我需要对复制的数据应用一些格式条件，我希望使用VBA提供一些建议:
The active cell "B" column
活动单元格“B”列
Less than 10 red Between 10 and 15 yellow More than 15 green
10到15之间的红色少于10，黄色多于15绿色
all the cell not returning any number should be blank
所有不返回的单元格应该是空的。
THX
谢谢
1 个解决方案
#1
1
There are two ways to do this:
有两种方法可以做到这一点:
The easy way:
Use Excel built-in conditional formatting (Select your range and click Home tab > Conditional Formatting > Add a rule or choose from the default rules -I think it has extensive options enough for your needs.)
使用Excel内置的条件格式(选择您的范围并单击Home选项卡>条件格式>添加规则或从默认规则中选择——我认为它有足够的选项来满足您的需要)。
The philosophical way:
Add a new module from your VBA IDE.
从VBA IDE中添加一个新模块。
Copy and paste this code:
复制粘贴此代码:
Sub ColorRange()
Dim d as Double
Dim r As Range
Set r = ActiveSheet.Range("B1:B500")
For Cell in r
If Cell.Text <> "" And IsNumeric(Cell.Value) = True Then
If Cell.Text < 10 Then
Cell.Interior.Color = RGB(255, 0, 0)
ElseIf Cell.Text >= 10 And Cell.Text <= 15 Then
Cell.Interior.Color = RGB(255, 255, 0)
Else
Cell.Interior.Color = RGB(0, 255, 0)
End If
End If
Next
End Sub
and run the macro.
和运行宏。
Here's an output sample: 
这里有一个输出样例:
Which one I recommend?
I would recommend you use conditional formatting, no need for the VBA magic if the good old built-in Excel features can handle it, unless your need prove otherwise.
我建议您使用条件格式，如果优秀的旧内置Excel功能能够处理它，则不需要使用VBA魔术，除非您需要证明其他情况。
#1
1
There are two ways to do this:
有两种方法可以做到这一点:
The easy way:
Use Excel built-in conditional formatting (Select your range and click Home tab > Conditional Formatting > Add a rule or choose from the default rules -I think it has extensive options enough for your needs.)
使用Excel内置的条件格式(选择您的范围并单击Home选项卡>条件格式>添加规则或从默认规则中选择——我认为它有足够的选项来满足您的需要)。
The philosophical way:
Add a new module from your VBA IDE.
从VBA IDE中添加一个新模块。
Copy and paste this code:
复制粘贴此代码:
Sub ColorRange()
Dim d as Double
Dim r As Range
Set r = ActiveSheet.Range("B1:B500")
For Cell in r
If Cell.Text <> "" And IsNumeric(Cell.Value) = True Then
If Cell.Text < 10 Then
Cell.Interior.Color = RGB(255, 0, 0)
ElseIf Cell.Text >= 10 And Cell.Text <= 15 Then
Cell.Interior.Color = RGB(255, 255, 0)
Else
Cell.Interior.Color = RGB(0, 255, 0)
End If
End If
Next
End Sub
and run the macro.
和运行宏。
Here's an output sample:
这里有一个输出样例:
Which one I recommend?
I would recommend you use conditional formatting, no need for the VBA magic if the good old built-in Excel features can handle it, unless your need prove otherwise.
我建议您使用条件格式，如果优秀的旧内置Excel功能能够处理它，则不需要使用VBA魔术，除非您需要证明其他情况。