[Tim]

Only way you could make this into a macro is if you downloaded 1 of those macro extender addons.

Also, if you're only going to be monitoring your own auras you can get rid of your 1st player check line and change your UNIT_AURA registering to frame:RegisterEvent("UNIT_AURA", "player")

Recommendation for your code though is doing what Kanegasi said and that is go to http://addon.bool.no/ then input all the data to create your own addon with your code.

Here's the changed stuff from what I mentioned about UNIT_AURA

local f = CreateFrame("Frame")
f:RegisterEvent("UNIT_AURA", "player")
f:SetScript("OnEvent", function(self, event, ...)
   if UnitBuff("player", "Nightfall") then
      SpellActivationOverlay_ShowOverlay(SpellActivationOverlayFrame, 248814, "TEXTURES\\SPELLACTIVATIONOVERLAYS\\GENERICTOP_01.BLP", "TOP", 1.2, 139, 65, 239, false, false)
   else
      SpellActivationOverlay_HideOverlays(SpellActivationOverlayFrame, 248814)
   end
end)