Excel 2016 conditional cells that are validated by data
I have searched thoroughly and cannot find this specific issue.
I will describe a workflow first, followed by a non-working case, and I think this will describe the problem I am trying to solve.
Work Case: Conditional Format Cells containing a formula:
1 - I have defined the name "ccell" which refers to "= ADDRESS (ROW (), COLUMN ())"
- It returns the address of the current cell
2 - I have defined the name "ccellval" which refers to "= INDIRECT (ccell)"
- It returns the value of the current cell
3 - I have defined the name "_IsFormula" which refers to "= ISFORMULA (ccellvalue)"
- It returns a boolean indicating that the current cell contains a formula
4 - I have a conditional format that uses the formula "= _IsFormula" and is applied at the row level (for example, it applies to "1: 10000")
The expected behavior is that whenever I enter a formula in a cell, that cell changes format - and it works great.
Broken Case: Conditional Format Cells with Data Validation
1 - I am using the existing specified name "ccell" mentioned above
- It returns the address of the current cell
2 - I have a UDF to return a boolean if a cell is validated by data:
Public Function Validated (ThisCell As Range) As Boolean
Dim v: v = Null
On Error Resume Next
v = ThisCell.Validation.Type
On Error GoTo 0
Validated = Not IsNull(v)
End Function
I have tested this inner cell UDF and it delivers the result TRUE when the cell is validated with data and #VALUE! when it isn't.
3 - I have defined the name "_IsValidated" which refers to "= Validated (ccell)"
- I intend to apply this UDF to the current cell = ccell
4 - I have a conditional format that uses the formula "= _IsValidated" and is applied at the row level (for example, it applies to "1: 10000")
The expected behavior is that anytime I add data validation to a cell, that cell changes format - and it doesn't work.
I have tried various permutations such as embedding ccell in my "verified" UDF rather than the "_IsValidated" specific name.
What am I trying to achieve with this? I am writing templates for users and I want the cells to be colored as a visual aid.
Any pointers / ideas would be greatly appreciated :) Thanks a lot!
source to share
Your problem is a lack of understanding of what returns ADDRESS
and INDIRECT
.
ADDRESS
returns a String
containing the cell address. And INDIRECT
does not return the cell value, but a cell reference. This is why your name ccellval
should be better ccellref
.
Yours UDF
Public Function Validated (ThisCell As Range) As Boolean
needs ThisCell
like Range
(like a cell reference), but you are passing ccell
which is String
.
So:
1 - Use existing specified name "ccell" mentioned above
- It returns the address of the current cell as
String
2 - The name "ccellref" is defined, which refers to "= INDIRECT (ccell)"
- It returns the reference of the current cell
3 - Ask the UDF to return a boolean if the cell is data validated.
4 - The name "_IsValidated" is defined, which refers to "= Validated (ccellref)"
5 - Conditional format that uses the formula "= _IsValidated"
source to share