ExcelBanter - lookup formatted cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - lookup formatted cell (https://www.excelbanter.com/excel-programming/343188-lookup-formatted-cell.html)

Kim

lookup formatted cell

I am trying to write a formula similar to vlookup. Basically, what I want
to do is write a formula or something that will look at a cell and if that
cell is highlighted blue, then the cell equals yes; if not then cell equals
no. This worksheet has over 10000 rows. Is this possible? I was looking
into writing a macro or something, but I don't know where to start.

Thanks
Kim

JS2004R6

lookup formatted cell

Hi Kim,

Here is some code that might work for you. It will look to see if each Cell
in range A1:A10000 has an interior ColorIndex of 5 (BLUE). If it does then it
will set the value of that cell to "Yes", otherwise "No". Simply change the
name of the sheet "Sheet1" and the range "A1:A10000" to meet your needs.

Hope it helps.

Regards,
James

Sub SetYesNoValue()
'DECLARATIONS
'------------
Dim wks As Worksheet
Dim rng As Range

'INITIALIZE
'----------
On Error GoTo ErrHandler
' Change the name of "Sheet1" below to your worksheet name.
Set wks = ThisWorkbook.Worksheets("Sheet1")

'MAIN BODY
'---------
Application.ScreenUpdating = False
' Change the value of the range "A1:A10000" to your range.
For Each rng In wks.Range("A1:A10000")
If rng.Interior.ColorIndex = 5 Then ' ColorIndex 5 = BLUE
rng.Value = "Yes"
Else
rng.Value = "No"
End If
Next rng
Application.ScreenUpdating = True
MsgBox "DONE"

'WRAP-UP
'-------
GoSub CleanUp
Exit Sub

'CLEAN-UP
'--------
CleanUp:
Application.ScreenUpdating = True
Set wks = Nothing
Set rng = Nothing
Return

'ERROR HANDLER
'-------------
ErrHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, _
vbOKOnly + vbInformation, "SetYesNoValue()"
GoSub CleanUp
End Sub

"Kim" wrote:

I am trying to write a formula similar to vlookup. Basically, what I want
to do is write a formula or something that will look at a cell and if that
cell is highlighted blue, then the cell equals yes; if not then cell equals
no. This worksheet has over 10000 rows. Is this possible? I was looking
into writing a macro or something, but I don't know where to start.

Thanks
Kim

Kim

lookup formatted cell

James,
The blue highighted cells already contain values, so changing the values to
yes will not help. Is there a way to have an adjacent cell equal to yes.
Basically, what I want is similar to an if statement. If cell XX is
highlighted blue, then yes, if not, then no.

Thanks
Kim

"JS2004R6" wrote in message
...
Hi Kim,

Here is some code that might work for you. It will look to see if each
Cell
in range A1:A10000 has an interior ColorIndex of 5 (BLUE). If it does then
it
will set the value of that cell to "Yes", otherwise "No". Simply change
the
name of the sheet "Sheet1" and the range "A1:A10000" to meet your needs.

Hope it helps.

Regards,
James

Sub SetYesNoValue()
'DECLARATIONS
'------------
Dim wks As Worksheet
Dim rng As Range

'INITIALIZE
'----------
On Error GoTo ErrHandler
' Change the name of "Sheet1" below to your worksheet name.
Set wks = ThisWorkbook.Worksheets("Sheet1")

'MAIN BODY
'---------
Application.ScreenUpdating = False
' Change the value of the range "A1:A10000" to your range.
For Each rng In wks.Range("A1:A10000")
If rng.Interior.ColorIndex = 5 Then ' ColorIndex 5 = BLUE
rng.Value = "Yes"
Else
rng.Value = "No"
End If
Next rng
Application.ScreenUpdating = True
MsgBox "DONE"

'WRAP-UP
'-------
GoSub CleanUp
Exit Sub

'CLEAN-UP
'--------
CleanUp:
Application.ScreenUpdating = True
Set wks = Nothing
Set rng = Nothing
Return

'ERROR HANDLER
'-------------
ErrHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, _
vbOKOnly + vbInformation, "SetYesNoValue()"
GoSub CleanUp
End Sub

"Kim" wrote:

I am trying to write a formula similar to vlookup. Basically, what I
want
to do is write a formula or something that will look at a cell and if
that
cell is highlighted blue, then the cell equals yes; if not then cell
equals
no. This worksheet has over 10000 rows. Is this possible? I was looking
into writing a macro or something, but I don't know where to start.

Thanks
Kim

Kim

lookup formatted cell

I wrote a custom function in VBA that worked

Function blue(cell)
If cell.Range("A1").Interior.ColorIndex = 5 Then
blue = "Yes"
Else
blue = "No"
End If
End Function

Thanks Again James

Unfortunately, I keep getting a
"Kim" wrote in message
...
James,
The blue highighted cells already contain values, so changing the values
to yes will not help. Is there a way to have an adjacent cell equal to
yes. Basically, what I want is similar to an if statement. If cell XX is
highlighted blue, then yes, if not, then no.

Thanks
Kim

"JS2004R6" wrote in message
...
Hi Kim,

Here is some code that might work for you. It will look to see if each
Cell
in range A1:A10000 has an interior ColorIndex of 5 (BLUE). If it does
then it
will set the value of that cell to "Yes", otherwise "No". Simply change
the
name of the sheet "Sheet1" and the range "A1:A10000" to meet your needs.

Hope it helps.

Regards,
James

Sub SetYesNoValue()
'DECLARATIONS
'------------
Dim wks As Worksheet
Dim rng As Range

'INITIALIZE
'----------
On Error GoTo ErrHandler
' Change the name of "Sheet1" below to your worksheet name.
Set wks = ThisWorkbook.Worksheets("Sheet1")

'MAIN BODY
'---------
Application.ScreenUpdating = False
' Change the value of the range "A1:A10000" to your range.
For Each rng In wks.Range("A1:A10000")
If rng.Interior.ColorIndex = 5 Then ' ColorIndex 5 = BLUE
rng.Value = "Yes"
Else
rng.Value = "No"
End If
Next rng
Application.ScreenUpdating = True
MsgBox "DONE"

'WRAP-UP
'-------
GoSub CleanUp
Exit Sub

'CLEAN-UP
'--------
CleanUp:
Application.ScreenUpdating = True
Set wks = Nothing
Set rng = Nothing
Return

'ERROR HANDLER
'-------------
ErrHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, _
vbOKOnly + vbInformation, "SetYesNoValue()"
GoSub CleanUp
End Sub

"Kim" wrote:

I am trying to write a formula similar to vlookup. Basically, what I
want
to do is write a formula or something that will look at a cell and if
that
cell is highlighted blue, then the cell equals yes; if not then cell
equals
no. This worksheet has over 10000 rows. Is this possible? I was
looking
into writing a macro or something, but I don't know where to start.

Thanks
Kim

Kim

lookup formatted cell

JS2004R6

lookup formatted cell

Hi Kim,

Yes. You can set the cell to the right of (or left, above, below, etc.) to
Yes/No. Change these lines of code:

From This
rng.Value = "Yes"
To This
rng.Offset(0,1).Value = "Yes"

From This
rng.Value = "No"
To This
rng.Offset(0,1).Value = "No"

Hope that helps.

Cheers,
James

"Kim" wrote:

James,
The blue highighted cells already contain values, so changing the values to
yes will not help. Is there a way to have an adjacent cell equal to yes.
Basically, what I want is similar to an if statement. If cell XX is
highlighted blue, then yes, if not, then no.

Thanks
Kim

"JS2004R6" wrote in message
...
Hi Kim,

Here is some code that might work for you. It will look to see if each
Cell
in range A1:A10000 has an interior ColorIndex of 5 (BLUE). If it does then
it
will set the value of that cell to "Yes", otherwise "No". Simply change
the
name of the sheet "Sheet1" and the range "A1:A10000" to meet your needs.

Hope it helps.

Regards,
James

Sub SetYesNoValue()
'DECLARATIONS
'------------
Dim wks As Worksheet
Dim rng As Range

'INITIALIZE
'----------
On Error GoTo ErrHandler
' Change the name of "Sheet1" below to your worksheet name.
Set wks = ThisWorkbook.Worksheets("Sheet1")

'MAIN BODY
'---------
Application.ScreenUpdating = False
' Change the value of the range "A1:A10000" to your range.
For Each rng In wks.Range("A1:A10000")
If rng.Interior.ColorIndex = 5 Then ' ColorIndex 5 = BLUE
rng.Value = "Yes"
Else
rng.Value = "No"
End If
Next rng
Application.ScreenUpdating = True
MsgBox "DONE"

'WRAP-UP
'-------
GoSub CleanUp
Exit Sub

'CLEAN-UP
'--------
CleanUp:
Application.ScreenUpdating = True
Set wks = Nothing
Set rng = Nothing
Return

'ERROR HANDLER
'-------------
ErrHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, _
vbOKOnly + vbInformation, "SetYesNoValue()"
GoSub CleanUp
End Sub

"Kim" wrote:

I am trying to write a formula similar to vlookup. Basically, what I
want
to do is write a formula or something that will look at a cell and if
that
cell is highlighted blue, then the cell equals yes; if not then cell
equals
no. This worksheet has over 10000 rows. Is this possible? I was looking
into writing a macro or something, but I don't know where to start.

Thanks
Kim

Kim

lookup formatted cell

Thanks Again James. I've been trying to teach myself excel VBA/macro
writing - still a newbie.

Kim

"JS2004R6" wrote in message
...
Hi Kim,

Yes. You can set the cell to the right of (or left, above, below, etc.) to
Yes/No. Change these lines of code:

From This
rng.Value = "Yes"
To This
rng.Offset(0,1).Value = "Yes"

From This
rng.Value = "No"
To This
rng.Offset(0,1).Value = "No"

Hope that helps.

Cheers,
James

"Kim" wrote:

James,
The blue highighted cells already contain values, so changing the values
to
yes will not help. Is there a way to have an adjacent cell equal to yes.
Basically, what I want is similar to an if statement. If cell XX is
highlighted blue, then yes, if not, then no.

Thanks
Kim

"JS2004R6" wrote in message
...
Hi Kim,

Here is some code that might work for you. It will look to see if each
Cell
in range A1:A10000 has an interior ColorIndex of 5 (BLUE). If it does
then
it
will set the value of that cell to "Yes", otherwise "No". Simply change
the
name of the sheet "Sheet1" and the range "A1:A10000" to meet your
needs.

Hope it helps.

Regards,
James

Sub SetYesNoValue()
'DECLARATIONS
'------------
Dim wks As Worksheet
Dim rng As Range

'INITIALIZE
'----------
On Error GoTo ErrHandler
' Change the name of "Sheet1" below to your worksheet name.
Set wks = ThisWorkbook.Worksheets("Sheet1")

'MAIN BODY
'---------
Application.ScreenUpdating = False
' Change the value of the range "A1:A10000" to your range.
For Each rng In wks.Range("A1:A10000")
If rng.Interior.ColorIndex = 5 Then ' ColorIndex 5 = BLUE
rng.Value = "Yes"
Else
rng.Value = "No"
End If
Next rng
Application.ScreenUpdating = True
MsgBox "DONE"

'WRAP-UP
'-------
GoSub CleanUp
Exit Sub

'CLEAN-UP
'--------
CleanUp:
Application.ScreenUpdating = True
Set wks = Nothing
Set rng = Nothing
Return

'ERROR HANDLER
'-------------
ErrHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, _
vbOKOnly + vbInformation, "SetYesNoValue()"
GoSub CleanUp
End Sub

"Kim" wrote:

I am trying to write a formula similar to vlookup. Basically, what I
want
to do is write a formula or something that will look at a cell and if
that
cell is highlighted blue, then the cell equals yes; if not then cell
equals
no. This worksheet has over 10000 rows. Is this possible? I was
looking
into writing a macro or something, but I don't know where to start.

Thanks
Kim

All times are GMT +1. The time now is 10:07 PM.