How to set the same background color for the same Excel content

 

There is a requirement that the background color of the same Excel content is the same, and the color of the line is changed when the content is different, and the effect is recorded, if you have any better solutions, please give me some advice. The procedure is as follows:

First, paste the data in the "2014 base drug code" column that is compared to sheet2 to remove duplicate items. Then, the effect of sheet2 is as follows:

Insert a new column in the first column of the sheet1 table and name it "group number". In the cell A2, enter the condition formula: = Vlookup (G2, sheet2! A: B, 2, false)

The parameter usage of each column in the formula Vlookup is as follows:

G2: Compare the sheet1 cell G2.

Sheet2! A: B: the query content is a ~ of sheet2 ~ The content of Column B.

2: The final result is sheet2! A: Obtain the content of Column B in Area B.

False: whether the search condition is simulated search or exact search. False indicates exact search, and true indicates fuzzy search.

Detailed usage: Baidu.

After the result is successful, the value of sheet2 is 1. For the content of other cells, double-click column A2 to copy the formula.

Effect

Select the macro on the "View" tab, click "new macro", enter the macro name, and save. Click View macro to open the macro editing window. Click "edit". Paste the following code:

Click "run" to achieve the same content background color and line color. The Code is as follows:

Sub setstyle () dim intfromrowno as long, inttorowno as long, intfromcolno as long, inttocolno as long, intcurrentid as longdim inttempid as long, intflag as long, intmod as long, intcolorindex as longdim ischanged as Boolean whether inttempid = 1 'intflag = 2' is changed: when the group number changes, the ID is automatically increased by 1, when the modulo of ID 2 is 1, you need to adjust the pattern (that is, change the pattern of each even number of groups) intfromrowno = 2' starting line number inttorowno = activesheet. usedrange. rows. count 'end row intfromcolno = 1' start column number inttocolno = activesheet. usedrange. columns. count 'end column number intcolorindex = 20' 20 light green; 15 gray; for I = intfromrowno to inttorowno' when the next group number is the same as the current one, the style remains unchanged intcurrentid = activesheet. cells (1) (I) ischanged = intcurrentid = inttempidif (ischanged = false) Then intflag = intflag + 1: inttempid = intcurrentid: intmod = intflag mod 2if (intmod = 1) then activesheet. range (cells (I, intfromcolno), cells (I, inttocolno )). interior. colorindex = intcolorindex: activesheet. range (cells (I, intfromcolno), cells (I, inttocolno )). font. bold = truenextend sub

 

How to set the same background color for the same Excel content