Excel Countif cell has diagonal border


Joined
Feb 23, 2018
Messages
66
Reaction score
24
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.
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
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?
 
Joined
Feb 21, 2018
Messages
127
Reaction score
47
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'...
 
Last edited:
Joined
Feb 23, 2018
Messages
66
Reaction score
24
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!)
 
Joined
Feb 21, 2018
Messages
127
Reaction score
47
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 ?
 
Joined
Feb 23, 2018
Messages
66
Reaction score
24
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!
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
127
Reaction score
47
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top