Conditional Formatting to make picture visible? VBA needed?

H

hinsdale1

Board Regular
Joined
Oct 7, 2011
Messages
60
My brain is starting to hurt from reading/experimenting and am not getting any closer. I was hoping one of the forum geniuses might have pity and lend me a hand?

I would like to make a graphic (same graphic) placed in about 100 cells appear in those cells only (and be invisible otherwise) when the following is true of the cell:

This is the conditional formatting formula:

=IF(ISNUMBER(FIND("F",VLOOKUP(D172,$D$3:$M$166,3,FALSE))),FALSE,IF(ISNUMBER(FIND("+",VLOOKUP(D172,$D$3:$M$166,3,FALSE))),FALSE,IF(ISNUMBER(FIND(":",VLOOKUP(D172,$D$3:$M$166,3,FALSE))),FALSE,TRUE)))

Applies to:

=$D$172:$D$222,$F$172:$F$222,$H$172:$H$222,$J$172:$J$222,$L$172:$L$222

The images (same graphic) are individually named for the cells in which they reside... and are named D172,D173,D175,... F172,F173,F174... etc.

The VBA code is completely stumping me (have to do with VBA right?).. any direction would be welcome and VERY much appreciated. Thanks in advance for any help you can provide!
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
In my effort to be thorough perhaps I made it seem more elaborate then necessary... basically I have:

1) several columns of names (D172:L193), each name cell also containing a flag graphic (named "D172", "D173"... for the corresponding cell)

2) For each cell (with name), I simply want to perform a Lookup of the name in a table and if a certain column contains either "F","+", or ":", then make the flag graphic invisible, otherwise have the flag graphic display.


Do I use some sort of "for each" loop or Worksheet_Calculation event? I am confused as to how best to accomplish this conditional formatting in VBA. Hoping for a little direction in the VBA coding (not my strongsuit)? Thanks in advance to any that have mercy on me and can throw me a little light!!
 
Last edited:
Upvote 0
Just a rough direction would be welcome. I have searched threads and tried for 6.5 hours and unfortunately am right where I started. I am just not sure even how to approach it properly in VBA?
 
Upvote 0
Can you put that formula in each of those cells?

Or, more simply,

=AND(ISERROR(FIND({"F","+",":"}, VLOOKUP(D172,$D$3:$M$166,3,FALSE))))
 
Upvote 0
Assuming so,

Code: 
Sub H1()
  Dim cell As Range
  
  For Each cell In Range("D172:D222,F172:F222,H172:H222,J172:J222,L172:L222").Cells
    ActiveSheet.Shapes(cell.Address(False, False)).Visible = cell.Value
  Next cell
End Sub
 
Upvote 0
Thanks for a lifeline!

There are peoples name in the cells that must be there... What I would like to do is peform a lookup (using each persons name) in a table (that is constantly updating), and depending upon results, then either display or hide a picture that has been inserted in the cell next to the name.
 
Upvote 0
Code: 
Sub H1()
  Dim wks           As Worksheet
  Dim cell          As Range
  Dim rFind         As Range
  Dim s             As String

  Set wks = ActiveSheet

  With wks
    For Each cell In .Range("D172:D222, F172:F222, H172:H222, J172:J222, L172:L222").Cells
      Set rFind = .Range("D3:D166").Find(what:=cell.Value2, MatchCase:=False, LookAt:=xlWhole)
      If Not rFind Is Nothing Then
        s = .Cells(rFind.Row, "F").Value2
        If InStr(s, "F") Or InStr(s, "+") Or InStr(s, ":") Then
          .Shapes(cell.Address(False, False)).Visible = False
        Else
          .Shapes(cell.Address(False, False)).Visible = True
        End If
      End If
    Next cell
  End With
End Sub
 
Upvote 0
THANK YOU very kindly.. you are definitely a scholar and a gentleman. I am about to try the code but realized that I misspoke in my original post, the images couldnt be named the same as the cell address.. and are instead named the cell address followed by the word "FLAG" - (i.e. D172FLAG, D173FLAG, etc). It would be great if you can let me know how I might tweak the code to accomodate these names? Otherwise i will try to research how to do it!

Very excited to try the script! I honestly can't thank you enough for your help on this.
 
Upvote 0
.Shapes(cell.Address(False, False) & "FLAG").Visible = False
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
151
kerm007
K
P
  • Solved
How to vlookup a general format cell and convert it to a date
Replies
4
Views
245
Fluff
Fluff
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
148
gtd526
gtd526
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
125
gtd526
gtd526
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
129
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,614
Messages
6,120,519
Members
448,968
Latest member
Ajax40

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top