conditional format based on data type

  • Thread starter Thread starter Bert
  • Start date Start date
B

Bert

Some of the cells in my spreadsheet contain dates, and some simply contain
an "x". (Some cells are blank as well.)
If the column contains an "x", I'd like it to be centered within the cell;
otherwise with the dates, the default flush right is fine.
I've tried a few things, and poked around for answers online, but am not
finding a solution.
Any suggestions would be appreciated!
Thanks.
Bert
 
Cell alignment is not a feature of Conditional Formatting.

You would have to go with a macro in my opinion.


Gord Dibben MS Excel MVP
 
About the only thing you can do is put a value of:

=" x "

in the cell, rather than

="x"

You'll have to adjust the number of spaces every time you change the
width of the cell or the font, though, so it is not very "automatic".
 
Or use VBA

Place this in a general module:

Sub CntrX()

For Each c In Selection
If UCase(c) = "X" Then
c.HorizontalAlignment = xlCenter
Else
c.HorizontalAlignment = xlRight
End If
Next c

End Sub

And this in the relevant worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, Range("A:A"))
' note change A:A to relevant column letters
If Not isect Is Nothing Then
Call CntrX
End If

End Sub
 
I should also have considered the requirement for general alignment if other
values than dates are used. Instead of

c.HorizontalAlignment = xlRight
use
c.HorizontalAlignment = xlGeneral
 
Thanks! I didn't realize this would be so complicated. I'll add your code
to the modules as indicated.
Thanks, again!
Bert
 
Thanks for your suggestion.
Bert

dranon said:
About the only thing you can do is put a value of:

=" x "

in the cell, rather than

="x"

You'll have to adjust the number of spaces every time you change the
width of the cell or the font, though, so it is not very "automatic".
Click to expand...
 
Thanks. I thought what I was asking would get a simple answer, but I see
from other responses as well as yours that that is not the case. Thanks for
your insights.
Bert
 
Back
Top