Shulou(Shulou.com)06/01 Report--

This article introduces what is the transparency filling method of data map in excel. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Excel (VBA) data map transparency filling method.

The steps of this method are less difficult than the previous one, but the VBA code involved is a little more complicated than the previous one.

1. The first thing we need to prepare is the world map material:

In this case, the data is as many as 191 countries, and each country's graphic name is "S" +" the first three letters uppercase ", which needs to be named one by one, of course, this process is very time-consuming and laborious, after push, I will contribute the source file of this example, if interested, you can directly try to do the vector graphics that have been named in the case.

2. Add transparency column variables

= ($E$1-D4) / ($E$1-$E$2) * 90%

Convert the index value to a transparency index of 0% to 90% according to the range of the index value.

3. Select the main color of the transparency fill as the theme color of the fill hue.

Fills the selected dominant color into the specified cell. (H3)

4. Insert a rectangle (named color_label) as the map legend

5. Enter the VBA color code

ALT+F11 opens the VBA editor and enters the following code:

Sub fill_color_vba ()

Application.CalculateFull 'for simulation effect

On Error Resume Next 'individual countries do not have graphics, ignore

Application.ScreenUpdating = False 'pauses to refresh the screen

For I = 4 To 193'is the start and end line numbers of the data source

ActiveSheet.Shapes (Range ("sheet1roomC" & I) .value). Fill.ForeColor.RGB = Range ("Sheet1roomH3") .Interior.Color

'fill the drawing with the selected color

ActiveSheet.Shapes (Range ("sheet1roomC" & I) .value). Fill.Transparency = Range ("Sheet1SecretE" & I). Value

'set the transparency of the drawing by the matching transparency value

Next i

ActiveSheet.Shapes ("color_label"). Fill.ForeColor.RGB = Range ("SHEET1roomH3"). Interior.Color

'set the fill color of the legend

ActiveSheet.Shapes ("color_label") Fill.TwoColorGradient msoGradientVertical, 2

'ActiveSheet.Shapes ("color_label"). Fill.OneColorGradient msoGradientVertical, 2,0.23

'set the gradient effect of the legend

Application.ScreenUpdating = True 'restore refresh screen

End Sub

6. Insert a button in the development tool and develop the macro code (named fill).

Then click the fill button to see the magical effect.

Finally, the inserted rectangle (as a map legend) is placed in the appropriate position of the data map, and the whole data map license plate is referenced using the camera snapshot function.

When saving, you still want to save the file with macros in xlsm format.

Just copy the main color of the fill color you prepared in advance into the fill cell, and then click the fill button to achieve the filling effect of different tones.

On the excel data map transparency filling method is what is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.