Application of VBA in Excel (1): change the background color of cells meeting the condition

When using EXCEL to process data, it is a good choice to add a background color to cells to clearly mark cells that meet specific conditions. Manual processing is simple and quick, but it takes a lot of time and effort to handle large volumes of data ). Code processing is a good choice. Excel can process internal data through VBA programming. After opening the Excel page, you can use the Alt + F11 combination key to start the VBA programming interface, like the VB programming interface and syntax, you must note how to call the Excel content. VBA uses the sheet, range, and cells layers to call the specific area in Excel. sheet specifies the table page to be processed. Ranges specifies the scope to be processed. It can be a cell, it can also be multiple cells. cells (I, j) locate the cells by creating row subscript I and column table J, through these three layers, you can basically locate any location in Excel.

For VBA and Excel programming, see: http://msdn.microsoft.com/zh-cn/library/ee814737.aspx

Http://www.cnblogs.com/jaxu/archive/2009/04/04/1407004.html

 

Example: When performing a confirmed factoe analysis (CFA), we usually use a factor load to determine the construct) the difference between validity (discriminant validity), that is, the correlation between items under the same structure is as high as possible, and the correlation between items without homogeneous reading is as low as possible. Therefore, when the correlation matrix is used to distinguish cells with specific background colors, the correlation between items can be clearly seen.

Suppose we have a 64*64 correlation matrix. In Excel, we use VBA to add the background color to cells with correlation values of different ranges. The Code is as follows:

1 sub changebgcolor () 2 dim I as integer 3 dim J as integer 4 dim R as integer 5 Dim C as integer 6 7 8 R = 67 'the last row is 67th rows 9 C = 66' the last column is an iteration of 66th columns 10 11 for I = 3 to R, from Row 1 to row 12 for J = 2 to C' iteration in the last row, from column 3rd to column 13 14 if cells (I, j)> 0.5 and cells (I, j) <0.6 then' if the cell (I, j) value is greater than 0.5 under 0.6, the background color code is 4215 cells (I, j ). interior. colorindex = 4216 end if17 18 if cells (I, j)> 0.6 and cells (I, j) <0.7 then19 cells (I, j ). interior. colorindex = 4320 end if21 22 if cells (I, j)> 0.7 and cells (I, j) <0.8 then23 cells (I, j ). interior. colorindex = 624 end if25 26 if cells (I, j)> 0.8 and cells (I, j) <1 then27 cells (I, j ). interior. colorindex = 328 end if29 next30 next31 32 end sub

The result is as follows:

 

Appendix 1: colorindex)