|
RT,代码添加后 每次在单元格change后,规则的值都会变化。。。。我这里是写死的啊 为什么会变成最大的行数。。。
try
{
//添加验证功能,在每次新增时后去将当前currentrange获取为当前范围,然后通过条件选择的公式追加突出显示的功能//
////////////////////新增一条记录后,用户对新增的记录做修改的同时,此突出显示将会生效///////////////////////
Excel.Worksheet ws = Globals.ThisWorkbook.Worksheets[5];
Range current_rang = Target.CurrentRegion;
object mis = System.Type.Missing;
if (current_rang.FormatConditions.Count > 0)
{
current_rang.FormatConditions.Delete();
}
int count = current_rang.Rows.Count;
//Range selectionrange = ws.get_Range((Range)ws.Cells[7, 2], (Range)ws.Cells[count+5, 4]);
//string text = "=SUMPRODUCT(($B$2:$B$" + (count).ToString() + "=$B2)*($C$2:$C$" + (count).ToString() + "=$C2)*($D$2:$D$" + (count).ToString() + "=$D2))>1";
string text = "=SUMPRODUCT(($B$7:$B$" + (count + 5).ToString() + "=$B7)*($C$7:$C$" + (count + 5).ToString() + "=$C7)*($D$7:$D$" + (count + 5).ToString() + "=$D7))>1";
Excel.FormatCondition cond =
(Excel.FormatCondition)current_rang.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression,
Excel.XlFormatConditionOperator.xlEqual, text,
mis, mis, mis, mis, mis);
cond.ModifyAppliesToRange(ws.get_Range((Excel.Range)ws.Cells[7, 4], (Excel.Range)ws.Cells[count + 5, 4]));
cond.Interior.PatternColorIndex = Excel.Constants.xlAutomatic;
cond.Interior.TintAndShade = 0;
cond.Interior.Color = XlRgbColor.rgbGreenYellow;
cond.StopIfTrue = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
效果如图
如上图 这是通过代码执行后的问题所在。
|
|