hi

[Luckyshetty]

i have spreadsheet in that 1st row should be in upper case, how do i format it so that even if text is typed in lower case it should go to upper case.

anybody help me on the same

 

[SuperFerret]

Something like this? (amend range as necessary)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("[COLOR=red]A2:A10[/COLOR]")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub

ALSO you are better off posting to the forum with meaningful title, it helps people see at a glance what you need help with and more people are inclinded to read your thread

 

[Ruddles]

I would approach this by detecting when the worksheet changes, checking whether any of the changes are in row 1 and then converting any changed cells in row 1 to upper case.

Place this in the worksheet code module for the worksheet where you want this to happen:-

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim oCell As Range
 
  If Not Intersect(Target, Rows(1)) Is Nothing Then
    For Each oCell In Intersect(Target, Rows(1))
      If oCell.Value <> UCase(oCell.Value) Then oCell = UCase(oCell.Value) 
    Next oCell
  End If
 
End Sub

The worksheet code module is accessed by right-clicking the worksheet tab and selecting View Code.

Macros have to be enabled for this to work.

Always test new code on a copy of your workbook.

 

[Luckyshetty]

Re: Conditional Formatting or data Validation

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
i have spreadsheet in that 1st row should be in upper case, how do i format it so that even if text is typed in lower case it should go to upper case.
No Macros

anybody help me on the same

 

[West Man]

What you are asking can not be done without a macro. Perhaps data valadation could be used to force input of all upper case. The data validation formula would be =EXACT(A1,UPPER(A1))