模型构建后，VBA（突出显示Excel中的Hardcode单元格（即1234）） [英] VBA (highlight Hardcode cell (i.e.1234) in Excel) after model is built

问题描述

我正在建立一个财务模型，我想在模型完成之后突出显示所有的单元格。在模型完全没有逐个搜索每个输入之后，我需要确定哪一个是硬编码的。

这将是非常好的，如果你可以帮助一个vba的整个excel选项卡和一个选定的范围在一张单。谢谢。

解决方案

我相信你所要求的是查看范围，然后突出显示该范围内的任何值不包含公式。所以首先找到要突出显示的范围，然后找到该范围内包含公式的范围。对于我的例子，我们会说你的模型是从单元格A1到A100

  Public Sub hightlightNoFormulas（）
 Dim yourRange as Range，rangeNoFormula as Range 
 Set yourRange = Range（A1：A100）
设置rangeNoFormula = yourRange.SpecialCells xlCellTypeFormulas 
  

然后循环遍历范围，排除任何具有公式的值

  Dim rng作为范围
对于您的Range 
中的每个rng如果Intersect（rng，rangeNoFormula）是Nothing然后
 rng.interior.Color = 65535 
结束如果
下一个rng 
退出子
  

I'm building a financial model and i'm trying to highlight all the cells after the model is complete. I need to identify which one are hardcoded after the model is completely without searching for each input one by one.

it would be great, If you can help with a vba for the whole excel tab and a selected range on a sheet. thank you.

解决方案

I believe what you are asking is to look through a range, and then highlight any values within that range that don't contain a formula. So first find the range you want to highlight and then find the ranges within that range that contain a formula. For my example we'll say that your model is from cells A1 to A100

Public Sub hightlightNoFormulas()
     Dim yourRange as Range, rangeNoFormula as Range
     Set yourRange = Range("A1:A100")
     Set rangeNoFormula = yourRange.SpecialCells xlCellTypeFormulas

Then loop through your range, excluding any values that have formulas

     Dim rng as Range
     For Each rng in yourRange
          If Intersect(rng,rangeNoFormula) Is Nothing Then
               rng.interior.Color = 65535
          End If
     Next rng
Exit Sub

这篇关于模型构建后，VBA（突出显示Excel中的Hardcode单元格（即1234））的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！