USER EMAIL : Conditional formatting using formula



Problem :


I have a small query and need your help...


How to do a conditional formatting of  a cell in excel that it accepts only any alphanumeric of 8 digits where the first 4 digits are alpha and next 4 digits are numeric in this fashion for e.g (QBCD1234) and in case the user ​feeds "0" inplace of Q it does not accept and a error message is given out...




Solution :



1. Open VBA editor and paste this code in a new module.

Function CFunc(str As String)

	If Len(str) > 4 Then
	     flag = True

            For i = 1 To 4

				If Not (Asc(Mid(UCase(str), i, 1)) > 64 And Asc(Mid(UCase(str), i, 1)) < 92)

					flag = False

				End If

			Next
	
		CFunc = flag

    Else

        CFunc = False

    End If

End Function
   
2. Select cell A1 and create a rule in Conditional Formatting. Then select  "Use a formula to determine which cells to format"
Enter formula : =cFunc(A1)
Click Format and set fill color to red
Copy this formula to other cells wherever applicable.
Output :
The cells where first four characters are alphabets then it will change the cell color to red.
Labels: , , , , , , , ,