Excel Countif cell has diagonal border

[Simont485]

HI

I have a range of cells that contain various letters but some have been crossed out using the diagonal border facility and appear to be 'crossed out' so to speak

I want to count the cells, using countif, that have the text and ignore the 'crossed out' ones.

 

[Becky]

There's no way I know of to do this with formulae, I think you would need to use VBA code. I'm not very good at that, but maybe @AmjiBhai can help?

 

[AmjiBhai]

There's no way I know of to do this with formulae, I think you would need to use VBA code. I'm not very good at that, but maybe @AmjiBhai can help?

Yes! using VBA i would develop a function that would skip the cells with a certain cell-formatting and do the countif/sumif etc...But most of the users like to avoid the involvement of VBA in such case they utilize filters...that is as it appears in another forum....How is the bordering caused... is it with Conditional formatting? If yes, you can use a similar formula in a new column that will give same result (TRUE/FALSE) and then filter for TRUE

BTW, I want to share you an interesting experience in my 30 years career. I believe that, to be that expert in VBA you start loosing your acceptability in your organization....people used to say ...such and such job please refer it to AmjiBhai, it only he can do this....it's only he can do this....and finally you are no more required....I lost couple of jobs just because "its only you can do this'...

 

[Simont485]

Thank you for your reply. The cross border is applied manually, select a range of cells, format etc...

I wont be utilizing filters so the vba code could be useful. If you could provide it and I will try it.

If it doesn't work for the current spreadsheet I will have to look at the way it is currently populated. (easier said than done though!)

 

[AmjiBhai]

Lets do it in two phases..

put this code in your VBA Module

Function IsDiagonalBorder(rng2 As Range) As Boolean

On Error Resume Next

Border = rng2.Borders(xlDiagonalDown).LineStyle

On Error GoTo 0


If Border = -4142 Then

IsDiagonalBorder = False

Else

IsDiagonalBorder = True

End If

End Function



Now let me know if you write a formula like this:-
=isdiagonalBorder(A25)

here i presume your cell A25 contains diagonal border....
Let me know do you get a TRUE or you get False ?

 

[Simont485]

Lets do it in two phases..

put this code in your VBA Module

Function IsDiagonalBorder(rng2 As Range) As Boolean

On Error Resume Next

Border = rng2.Borders(xlDiagonalDown).LineStyle

On Error GoTo 0


If Border = -4142 Then

IsDiagonalBorder = False

Else

IsDiagonalBorder = True

End If

End Function



Now let me know if you write a formula like this:-
=isdiagonalBorder(A25)

here i presume your cell A25 contains diagonal border....
Let me know do you get a TRUE or you get False ?

I will do this when I'm back at work and let you know. I'm at work this weekend!

 

[AmjiBhai]

I will do this when I'm back at work and let you know. I'm at work this weekend!

please ignore previous VBA....
replace it with this one...i have simplified it.

Function IsDiagonalBorder(rng2 As Range)

On Error Resume Next

Border = IIf(rng2.Borders(xlDiagonalDown).LineStyle = 1, 1, IIf(rng2.Borders(xlDiagonalUp).LineStyle = 1, 2, 0))

On Error GoTo 0



IsDiagonalBorder = Border


End Function


You should get 0 for no diagonal border, 1 or 2 for any diagonal border...